﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Net;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Text.RegularExpressions;
using System.Reflection;
using FarPoint.Win.Spread;
using FarPoint.Win.Spread.CellType;
using System.Data.SqlClient;
using System.Security.Cryptography;

using Microsoft.Win32;//操作注册表用

namespace SqlServerTools
{
    public partial class Form1 : Form
    {
        private class RowInfo
        {
            ///// <summary>生成SQL语句时是否包含该字段，默认值为true </summary>
            //[Description("是否有效")] 
            //public Boolean YesEffective { get; set; }

            /// <summary>字段说明 </summary>
            [Description("字段说明")] 
            public String ColCaption { get; set; }
            
            /// <summary>字段名 </summary>
             [Description("字段名")] 
            public String ColName { get; set; }


             private String _ColType;
            /// <summary>字段类型 </summary>
             [Description("字段类型")] 
            public String ColType 
             { 
                 get{return this._ColType;}
                 set
                 {
                     if (new Regex(@"^(" + RowInfo.EnColType.NUMERIC.ToString() + @")|^(" + RowInfo.EnColType.INT.ToString() + ")").IsMatch(value.ToString().ToUpper()) == true)
                     {
                         this.EnableNull = false;
                         this.DefultValue = "0";
                     }
                     if (new Regex(@"^(" + RowInfo.EnColType.DATETIME.ToString() + ")").IsMatch(value.ToString().ToUpper()) == true)
                     {
                         this.EnableNull = false;
                         this.DefultValue = "2000-01-01 00:00:00";
                     }

                     if (new Regex(@"^(" + RowInfo.EnColType.DATE.ToString() + ")").IsMatch(value.ToString().ToUpper()) == true)
                     {
                         this.EnableNull = false;
                         this.DefultValue = "2000-01-01";
                     }
                     if (new Regex(@"^(" + RowInfo.EnColType.NVARCHAR.ToString() + ")").IsMatch(value.ToString().ToUpper()) == true)
                     {
                         if (this.YesPrimaryKey == false)
                         {
                             this.EnableNull = true;
                             this.DefultValue = "";
                         }
                         
                     }
                     this._ColType = value;
                 }
                    
             }

             private Boolean _YesPrimaryKey = false;
            /// <summary>是否主键,默认值：Flase </summary>
             [Description("主键")]
             public Boolean YesPrimaryKey
             {
                 get { return this._YesPrimaryKey; }
                 set 
                 { 
                     if (value == true)
                     {
                         this.EnableNull = false;
                         this.IndexType = EnIndexType.DESC.ToString();
                     }
                     this._YesPrimaryKey = value;
                 } 
             }

             private Boolean _EnableNull = true;
            /// <summary>可空,默认值为true</summary>
             [Description("可空")] 
            public Boolean EnableNull
             {
                 get { return this._EnableNull; }
                 set
                 {
                     if (value == false)
                     {
                         if (this.YesPrimaryKey == true) 
                         {
                             MessageBox.Show("主键不能为空!");
                             return;
                         }
                     }
                     this._EnableNull = value;
                 }
             }

             private String _IndexType = EnIndexType.Null.ToString();
            /// <summary>索引, 默认值：空</summary>
             [Description("索引")] 
            public String IndexType
             {
                 get { return this._IndexType; }
                 set
                 {

                     if (value.ToUpper() == EnIndexType.Null.ToString().ToUpper())
                     {
                         if (this.YesPrimaryKey == true)
                         {
                             MessageBox.Show("主键索引类型不能为NULL!");
                             return;
                         }
                     }
                     this._IndexType = value;
                 }
             }
            /// <summary>默认值</summary>
             [Description("默认值")] 
            public String DefultValue { get; set; }

            

            public RowInfo()
            {
                IndexType = EnIndexType.Null.ToString();
                YesPrimaryKey = false;
                DefultValue = "";
                ColType = "Nvarchar(50)".ToUpper();
                DefultValue = "";
                IndexType = EnIndexType.Null.ToString();
                EnableNull = false;
                //YesEffective = true;
            }

            //生成一个主键行信息
            public RowInfo(Boolean yesPrimaryKey,String tableCaption)
            {
                
                EnableNull = false;
                IndexType = EnIndexType.DESC.ToString();
                ColCaption = tableCaption + "编号";
                ColName = "Code";
                ColType = "NVARCHAR(15)";
                YesPrimaryKey = true;
                //YesEffective = true;

            }


            public RowInfo(String strColCaption) 
            {
                
                YesPrimaryKey = false;
                EnableNull = true;
                IndexType = EnIndexType.Null.ToString();
                DefultValue = "";
                //YesEffective = true;

                strColCaption = strColCaption.Trim();
                if (String.IsNullOrEmpty(strColCaption)) { return; }

                ColCaption = strColCaption;
                
                ColName = BaiduTans_Get(strColCaption).Replace(".","").Trim();
                ColName = String.Concat(ColName.Split(' ').Select(r => r.Trim().Substring(0, 1).ToUpper() + r.Trim().Substring(1).ToLower()).ToArray());

                ColType = "Nvarchar(50)".ToUpper();

                if (new Regex(@"(数量)$|量$|数$|(价格)$|(价)$|(费)$|(额)$|(总计)$"
                    + "|(小计)$|(合计)$|(比率)$|(率)$|(比重)$|(比)$"
                    + "|(工资)$|(奖金)$|(税金)$|(返利)$"
                    + "|(毛利)$|(费)$|(费用)$|(提成)$"
                    + "|值$|(面积)$"

                    ).IsMatch(strColCaption) == true)
                {
                    ColType = "Numeric(18,4)".ToUpper();
                    DefultValue = "0";
                }
                if (new Regex(@"(得分)$|(成绩)$|(系数)$" ).IsMatch(strColCaption) == true)
                {
                    ColType = "Numeric(18,2)".ToUpper();
                    DefultValue = "0";
                }
                if (new Regex(@"(时间)$").IsMatch(strColCaption) == true) 
                {
                    ColType = "DateTime".ToUpper();
                    DefultValue = "2000-01-01 00:00:00";
                }

                if (new Regex(@"(日期)$").IsMatch(strColCaption) == true) 
                {
                    ColType = "Date".ToUpper();
                    DefultValue = "2000-01-01";
                }


                //对字符型字段的特殊处理
                if (ColType == "Nvarchar(20)".ToUpper())
                {

                    if (new Regex(@"^(是否)").IsMatch(strColCaption) == true) //只有符型的字段才可以用是否之类的默认值
                    {
                        ColType = "Nvarchar(10)".ToUpper();
                        DefultValue = "否";
                    }
                    if (new Regex(@"(编号)$").IsMatch(strColCaption) == true) //只有符型的字段才可以用是否之类的默认值
                    {
                        if (strColCaption.Length > 2)
                        {
                            ColName =  BaiduTans_Get(strColCaption.Substring(0, strColCaption.Length - 2)).Replace(".", "") + " Code";
                            ColName = String.Concat(ColName.Split(' ').Select(r => r.Trim().Substring(0, 1).ToUpper() + r.Trim().Substring(1).ToLower()).ToArray());
                            ColType = "NVARCHAR(15)";
                        }
                    }
                    if (new Regex(@"(密码)$").IsMatch(strColCaption) == true)
                    {
                        DefultValue = "123456";
                        EnableNull = false;
                    }
                    if (new Regex(@"(状态)$").IsMatch(strColCaption) == true)
                    {
                        ColType = "Nvarchar(20)".ToUpper();
                        if (strColCaption.Length > "状态".Length)
                        {
                            DefultValue = "待" + strColCaption.Substring(0, strColCaption.Length - "状态".Length);
                        }
                        else
                        {
                            DefultValue = "正常";
                        }
                    }

                }


            }


            /// <summary>调用百度翻译服务将中文翻译成英文 </summary>
            /// <param name="q">汉语</param>
            /// <returns>英语</returns>
            private String BaiduTans_Get(String q)
            {
                try
                {
                    //string strClient_id = "vG6AXgnTtYV9c0HsDpWdTZAI";
                    string strFrom = "zh";
                    String strTo = "en";

                    String appid = "20151127000006782";
                    String sec_key = "_qlXmecyWtZS70KUIeNR";
                    String salt = "8080";


                    #region sign的计算方法：
                    #endregion



                    String sign = appid + q + salt + sec_key;
                    sign = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sign, "MD5").ToLower();   //32MD5加密

                    q = System.Web.HttpUtility.UrlEncode(q);

                    String url = string.Format("http://api.fanyi.baidu.com/api/trans/vip/translate?q={0}&from={1}&to={2}&appid={3}&salt={4}&sign={5}",
                        q, strFrom, strTo, appid, salt, sign);
                    WebClient wc = new WebClient();
                    String strJson2 = wc.DownloadString(url);
                    JObject Obj = JObject.Parse(strJson2);
                    return Obj["trans_result"][0]["dst"].ToString();
                }
                catch (Exception ex)
                {
                    return ex.Message;
                }
            }



            /// <summary>索引类型</summary>
            public enum EnIndexType { Null,ASC,DESC }

            /// <summary>字段类型 </summary>
            public enum EnColType { NVARCHAR,DATE,DATETIME,NUMERIC,INT }

            /// <summary>属性说明</summary>
            public enum EnDescription { 字段说明, 字段名, 字段类型, 主键,  可空, 索引, 默认值 }

        }


        public Form1()
        {
            InitializeComponent();
        }





        #region tab1相关事件
        private List<String> lstDescription = new System.Collections.Generic.List<string>();

        private void Form1_Load(object sender, EventArgs e)
        {
            String strIPs = GetRegInfo("IP");
            if (String.IsNullOrEmpty(strIPs) == true)
            { strIPs = "127.0.0.1"; }
            combIP.Items.AddRange(strIPs.Split(','));
            combIP.SelectedIndex = 0;

            txtUser.Text = this.AES_Decrypt(GetRegInfo("DataBaseUser"));
            txtPassWord.Text = this.AES_Decrypt(GetRegInfo("DataPassWord"));


            txtFolder.Text = GetRegInfo("EntityClassSaveFolder");


            
            fpSTableInfo.EditModeReplace = true;//当表格被编辑时，自动更新其数据源
            combReplaceTxt.SelectedIndex = 0;

            lstValueName.SelectedIndex = 0;
            tabControl1.SelectTab("tabPage2");

            FpsFields.Sheets[0].OperationMode = OperationMode.ReadOnly;
            FpsTableS.Sheets[0].OperationMode = OperationMode.ReadOnly;
            //FpsTabelData.Sheets[0].OperationMode = OperationMode.Normal;
            //FpsTabelData.Sheets[0].

        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            String strIPs = String.Join(",",combIP.Items.Cast<String>().Select(r=>r).ToArray<String>());
            SetRegInfo("IP",strIPs);
            SetRegInfo("DataBaseUser", this.AES_Encrypt(txtUser.Text));//加密存储用户和密码
            SetRegInfo("DataPassWord", this.AES_Encrypt(txtPassWord.Text));
            SetRegInfo("EntityClassSaveFolder", txtFolder.Text);


        }


        private void bntGetTabeleStructure_Click(object sender, EventArgs e)
        {
            GetTabeleStructure();
        }

        private void bntGetSqlStr_Click(object sender, EventArgs e)
        {
            GetSqlStr();
        }

        private void bntGetSqlList_Click(object sender, EventArgs e)
        {
            GetSqlList();
        }

        private void bntRunSql_Click(object sender, EventArgs e)
        {
            ExeCreateTableSqlList();
        }



        private void fpSTableInfo_MouseUp(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            { fpSTableInfo.Sheets[0].RemoveRows(fpSTableInfo.Sheets[0].ActiveRow.Index, 1); }
        }

        /// <summary>表注释发生变化时 </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void txtTableCaption_TextChanged(object sender, EventArgs e)
        {

            String strTableCaption = txtTableCaption.Text.Trim();
            if (strTableCaption == "") { return; }
            String strDetail = "";
            if (new Regex(@"(从表)$").IsMatch(strTableCaption) == true)
            {
                strTableCaption = strTableCaption.Substring(0,
                    strTableCaption.Length - "从表".Length);
                strDetail = "Detail";
            }

            String strTableName = BaiduTans_Get(strTableCaption);
            String[] tempStrs = strTableName.Split(' ');
            txtTableName.Text = String.Concat(tempStrs.Select(r => r.Substring(0, 1).ToUpper() + r.Substring(1).ToLower()).ToArray());
            txtTableName.Text = txtTableName.Text + strDetail;

        }

        private void txtTableCaption_Click(object sender, EventArgs e)
        {
            if (String.IsNullOrWhiteSpace(txtTableCaption.Text) == true)
            {
                txtTableCaption.Text = Clipboard.GetText();
            }
        }

        private void txtColCaptionS_Click(object sender, EventArgs e)
        {
            if (String.IsNullOrWhiteSpace(txtColCaptionS.Text) == true)
            {
                txtColCaptionS.Text = Clipboard.GetText();
            }
        }

        private void txtColCaptionS_TextChanged(object sender, EventArgs e)
        {
            if (chkAutoGetTabeleStructure.Checked == false) { return; }
            
            //智能自适应
            //txtColCaptionS.Text=txtColCaptionS.Text.Replace("、", ",").Replace("，", ",");
            //if (txtColCaptionS.Text.EndsWith(",") == true) { txtColCaptionS.Text = txtColCaptionS.Text.Substring(0, txtColCaptionS.Text.Length - 1); }
            GetTabeleStructure();
            txtColCaptionS.SelectAll();



        }


        //将txtColCaptionS中选中的文本替换为combReplaceTxt选中的文本

        private void combReplaceTxt_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (combReplaceTxt.SelectedIndex < 0 || txtColCaptionS.SelectionLength<1) { return; }
            txtColCaptionS.Text = txtColCaptionS.Text.Replace(txtColCaptionS.SelectedText, combReplaceTxt.SelectedItem.ToString());
            combReplaceTxt.SelectedIndex = 0;
        }


        //将fpSTableInfo指定行的colName节改变ParentCode
        private void bntChangeToParentCode_Click(object sender, EventArgs e)
        {
            if (fpSTableInfo.Sheets[0].Rows.Count < 1) { return; }
            fpSTableInfo.Sheets[0].Cells[
                    fpSTableInfo.Sheets[0].ActiveRow.Index,
                    fpSTableInfo.Sheets[0].Columns[RowInfo.EnDescription.字段名.ToString()].Index].Text = "ParentCode";
            fpSTableInfo.Sheets[0].Cells[
                    fpSTableInfo.Sheets[0].ActiveRow.Index,
                    fpSTableInfo.Sheets[0].Columns[RowInfo.EnDescription.索引.ToString()].Index].Text = RowInfo.EnIndexType.ASC.ToString();


            //RowInfo row = (fpSTableInfo.DataSource as List<RowInfo>)[fpSTableInfo.Sheets[0].ActiveRow.Index];
            //row.ColName = "ParentCode";
            //row.IndexType = RowInfo.EnIndexType.ASC.ToString();
            
        }

        //添加一个附件列
         private void bntAddAnnexCol_Click(object sender, EventArgs e)
        {

                RowInfo annexCol = new RowInfo();
                annexCol.ColCaption = "附件_(上传型)";
                annexCol.ColName = "Annex";
                annexCol.EnableNull = true;
                annexCol.ColType = "Nvarchar(500)";
                List<RowInfo> lst = fpSTableInfo.DataSource as List<RowInfo>;
                int newRowIndex = fpSTableInfo.Sheets[0].ActiveRow.Index==-1?0:
                   fpSTableInfo.Sheets[0].ActiveRow.Index + 1;

                lst.Insert(newRowIndex, annexCol);
                fpSTableInfo.Sheets[0].DataSource = null;
                fpSTableInfo.Sheets[0].DataSource = lst;
                //STableInfo.Sheets[0].r
 

        }


        //将生成的SQL String 放入剪切板
        private void txtCreateTableSql_Click(object sender, EventArgs e)
        {
            Clipboard.SetText(txtCreateTableSql.Text);
        }


        /// <summary>此表为从表，打钩或取销时的处理 </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void chkYesDetailTable_CheckedChanged(object sender, EventArgs e)
        {
            if (String.IsNullOrWhiteSpace(txtTableCaption.Text) | String.IsNullOrWhiteSpace(txtTableName.Text))
            { 
                chkYesDetailTable.Checked = false;
                return;
            }
            if (chkYesDetailTable.Checked == true)
            {
                if (new Regex(@"(从表)$").IsMatch(txtTableCaption.Text) == false)
                {
                    //txtTableName.Text = BaiduTans_Get(txtTableCaption.Text) + "Detail";
                    txtTableCaption.Text = txtTableCaption.Text.Trim() + "从表";
                }
            }
            else
            {
                if (new Regex(@"(从表)$").IsMatch(txtTableCaption.Text) == true)
                {
                    txtTableCaption.Text = txtTableCaption.Text.Substring(0,
                        txtTableCaption.Text.Length - "从表".Length);                   
                }
            }
            chkAutoAddParentCode.Checked = chkYesDetailTable.Checked;
        }


        /// <summary>根据txtColCaptionS的内容生成待编辑的表结构 </summary>
        private void GetTabeleStructure()
        {
            //对数据绑定
            if (String.IsNullOrEmpty(txtColCaptionS.Text.Trim())){return;}
            String strColCaptionS = txtColCaptionS.Text;
            //智能自适应//Replace自动适应半角逗号
            strColCaptionS = strColCaptionS.Replace("、", ",").Replace("，", ",");
            if (strColCaptionS.EndsWith(",") == true) { strColCaptionS = strColCaptionS.Substring(0, strColCaptionS.Length - 1); }
            String[] captions = strColCaptionS.Split(',');
            
            progressBar1.Maximum = captions.Length;
            progressBar1.Value = 0;
            progressBar1.Visible = true;
            lstDescription.Clear();
            List<RowInfo> lst = captions.Select(
                r =>
                {
                    RowInfo Row = new RowInfo(r.Replace(" ",""));//去除所有空格
                    progressBar1.Value = progressBar1.Value + 1;
                    return Row;
                }
                ).ToList();
            progressBar1.Visible = false;
            //生成例信息
            Type tyClassInfo = typeof(RowInfo);
            PropertyInfo[] proPerS = tyClassInfo.GetProperties();

            lstDescription = proPerS.Select(r =>
                ((DescriptionAttribute)r.GetCustomAttributes(typeof(DescriptionAttribute), true)[0]).Description
            ).ToList();

            Int32 length = lstDescription.Count;
            ComboBoxCellType cmbocell;

            #region 处理 表名、主键、parentCode键
            if (txtTableName.Text.Trim() == "") { txtTableCaption_TextChanged(null, null); }
            String strTableName = txtTableName.Text;

            //设置主键
            if (chkAutoAddCode.Checked == true)
            {
                RowInfo mkeyRow = new RowInfo(true, txtTableCaption.Text);
                lst.Insert(0, mkeyRow);
                //fpSTableInfo.Sheets[0].Rows.Add(0, 1);
                //fpSTableInfo.Sheets[0].Rows.Count++;
            }
            else
            {
                //当第一个行以"编号结尾时",则视该字段为主键
                if (new Regex(@"(编号)$").IsMatch(lst[0].ColCaption) == true)
                {
                    //fpSTableInfo.Sheets[0].Cells[0, fpSTableInfo.Sheets[0].Columns["主键"].Index].Value = true;
                    //fpSTableInfo.Sheets[0].Cells[0, fpSTableInfo.Sheets[0].Columns["可空"].Index].Value = false;
                    lst[0].ColName = "Code";
                    lst[0].YesPrimaryKey = true;
                    lst[0].EnableNull = false;
                }
            }
            //设置从表parentCode
            if (chkAutoAddParentCode.Checked == true)
            {
                RowInfo parentCodeRow = new RowInfo();
                parentCodeRow.ColName = "ParentCode";
                parentCodeRow.ColCaption = txtTableCaption.Text.EndsWith("从表") == true
                    ? txtTableCaption.Text.Substring(0, txtTableCaption.Text.Length - "从表".Length) : txtTableCaption.Text;
                parentCodeRow.ColCaption = "所属" + parentCodeRow.ColCaption + "编号";
                parentCodeRow.IndexType = RowInfo.EnIndexType.ASC.ToString();
                lst.Insert(1, parentCodeRow);
                //fpSTableInfo.Sheets[0].Rows.Add(1, 1);
            }
            //自动添加备注
            if (chkAutoAddRemarks.Checked == true)
            {
                if (new Regex("备注").IsMatch(txtColCaptionS.Text) == false)
                {
                    RowInfo remarksRow = new RowInfo();
                    remarksRow.ColName = "Remarks";
                    remarksRow.ColCaption = "备注";
                    remarksRow.EnableNull = true;
                    lst.Add(remarksRow);
                }
            }
            if (chkAutoAddAnnex.Checked == true)
            {
                RowInfo annexRow = new RowInfo();
                annexRow.ColCaption = "附件_(上传型)";
                annexRow.ColName = "Annex";
                annexRow.EnableNull = true;
                annexRow.ColType = "Nvarchar(500)";
                lst.Add(annexRow);
            }
            #endregion

            fpSTableInfo.Sheets[0].Rows.Clear();
            fpSTableInfo.Sheets[0].Columns.Clear();
            fpSTableInfo.Sheets[0].DataSource = lst;
            //fpSTableInfo.DataSource = lst;

            #region 绑定后对表的列进行微调
            for (int i = 0; i < length; i++)
            {
                if (lstDescription[i] == "字段类型" || lstDescription[i] == "索引")
                {
                    cmbocell = new ComboBoxCellType();
                    if (lstDescription[i] == "字段类型")
                    {
                        cmbocell.Items = new String[] { "NVARCHAR(50)", "NUMERIC(18,4)"
                        , "DATETIEM", "DATA", "NVARCHAR(15)","NVARCHAR(20)", "NVARCHAR(200)", "NVARCHAR(500)", "NUMERIC(18,2)", "INT","SMALLINT" };
                        fpSTableInfo.Sheets[0].Columns[i].Width = 100;
                        cmbocell.Editable = true;

                    }
                    if (lstDescription[i] == "索引")
                    {
                        cmbocell.Items = new String[] {RowInfo.EnIndexType.Null.ToString(),
                        RowInfo.EnIndexType.ASC.ToString(),RowInfo.EnIndexType.DESC.ToString()};
                        fpSTableInfo.Sheets[0].Columns[i].Width = 60;
                        cmbocell.Editable = true;

                    }
                    cmbocell.AcceptsArrowKeys = FarPoint.Win.SuperEdit.AcceptsArrowKeys.AllArrows;
                    cmbocell.AutoSearch = FarPoint.Win.AutoSearch.SingleCharacter;

                    //cmbocell.ListAlignment = FarPoint.Win.ListAlignment.Left;
                    //cmbocell.ListOffset = 20;
                    //cmbocell.ListWidth = 0;
                    //cmbocell.MaxDrop = 2;
                    cmbocell.AutoCompleteMode = AutoCompleteMode.Suggest;
                    cmbocell.AutoCompleteSource = AutoCompleteSource.ListItems;

                    fpSTableInfo.Sheets[0].Columns[i].CellType = cmbocell;

                }

                if (fpSTableInfo.Sheets[0].Columns[i].CellType is CheckBoxCellType)
                { fpSTableInfo.Sheets[0].Columns[i].HorizontalAlignment = CellHorizontalAlignment.Center; }

                fpSTableInfo.Sheets[0].Columns[i].Tag = lstDescription[i];
                fpSTableInfo.Sheets[0].ColumnHeader.Cells[0, i].Text = lstDescription[i];


            }
            #endregion
            #region 对单独的每一行进行微调
            length = lst.Count;
            for (int i = 0; i < length; i++)
            {
                if (new Regex(@"^是否").IsMatch(lst[i].ColCaption) == true)
                {
                    cmbocell = new ComboBoxCellType();
                    cmbocell.Items = new String[] { "否", "是" };
                    cmbocell.AcceptsArrowKeys = FarPoint.Win.SuperEdit.AcceptsArrowKeys.AllArrows;
                    cmbocell.AutoSearch = FarPoint.Win.AutoSearch.SingleCharacter;
                    cmbocell.Editable = true;
                    //cmbocell.ListAlignment = FarPoint.Win.ListAlignment.Left;
                    //cmbocell.ListOffset = 20;
                    //cmbocell.ListWidth = 0;
                    //cmbocell.MaxDrop = 4;
                    fpSTableInfo.Sheets[0].Cells[i,
                    fpSTableInfo.Sheets[0].Columns["默认值"].Index].CellType = cmbocell;
                }
                if (new Regex(@"状态$").IsMatch(lst[i].ColCaption) == true)
                {
                    cmbocell = new ComboBoxCellType();
                    cmbocell.Items = new String[] { "正常", "在售", "待确认","待审批","待执行", "待收款", "待付款"
                        , "待入库","未到帐","未到","未开票"
                        , "停售","终止","离职","已确认","已审批", "已执行","已收款"
                        , "已付款","已入库" ,"已到帐","已到","已开票"};
                    cmbocell.AcceptsArrowKeys = FarPoint.Win.SuperEdit.AcceptsArrowKeys.AllArrows;
                    cmbocell.AutoSearch = FarPoint.Win.AutoSearch.SingleCharacter;
                    cmbocell.Editable = true;
                    //cmbocell.ListAlignment = FarPoint.Win.ListAlignment.Left;
                    //cmbocell.ListOffset = 20;
                    //cmbocell.ListWidth = 0;
                    //cmbocell.MaxDrop = 4;
                    fpSTableInfo.Sheets[0].Cells[i,
                    fpSTableInfo.Sheets[0].Columns["默认值"].Index].CellType = cmbocell;
                }

            }
            #endregion

            fpSTableInfo.Sheets[0].SetActiveCell(0, fpSTableInfo.Sheets[0].Columns["字段类型"].Index);
            fpSTableInfo.Focus();

            if (chkAutoGetSql.Checked)
            {
                GetSqlStr();
                GetSqlList();
            }
            foreach (Row row in fpSTableInfo.Sheets[0].Rows) { row.Height = 30; }


        }

        /// <summary>根据表格结构生成待执行的SQLStr</summary>
        private void GetSqlStr()
        {
            String strPrimaryKey = "Code";
            String strTableName;
            String strTableCaption;
            String strIndexType = "　DESC";


            strTableName = txtTableName.Text;
            strTableCaption = txtTableCaption.Text;


            System.Text.StringBuilder stbDescription = new StringBuilder();//用于存入生成注释的语句
            stbDescription.AppendLine("----字段说明语句-------");

            System.Text.StringBuilder stbDefult = new StringBuilder();//用于存入生成默认值的语句
            stbDefult.AppendLine("----字段默认值的语句-------");

            System.Text.StringBuilder stbIndex = new StringBuilder();//用于存入生成默认值的语句
            stbIndex.AppendLine("---创建索引的语句------");

            System.Text.StringBuilder stbSql = new StringBuilder();
            stbSql.AppendLine("----删除原对象-------");
            stbSql.AppendLine("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo." + strTableName + "') AND type in (N'U'))");

            stbSql.AppendLine("DROP TABLE dbo." + strTableName);
            stbSql.AppendLine("Go");
            stbSql.AppendLine("----创建新表-------");
            stbSql.AppendLine("CREATE TABLE [" + strTableName + "](");

            List<RowInfo> lst = (List<RowInfo>)fpSTableInfo.DataSource;


            Int32 length = lst.Count;
            for (int i = 0; i < length; i++)
            {
                stbSql.AppendLine(String.Concat("[",
                    lst[i].ColName, "] ", lst[i].ColType, " ", lst[i].EnableNull == true ? "NULL," : "NOT NULL,"));
                if (lst[i].YesPrimaryKey == true) { strPrimaryKey = lst[i].ColName; strIndexType = lst[i].IndexType.ToString(); }


                //字段注释
                stbDescription.AppendLine("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'" + lst[i].ColCaption
                    + "' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'" + strTableName
                    + "', @level2type=N'COLUMN',@level2name=N'" + lst[i].ColName + "'");
                stbDescription.AppendLine("Go");

                //字段默认值
                if (String.IsNullOrEmpty(lst[i].DefultValue) == false)
                {
                    stbDefult.AppendLine(
                        String.Concat("ALTER TABLE dbo.", strTableName, " ADD  CONSTRAINT DF_", strTableName, "_", lst[i].ColName, "  DEFAULT (N'", lst[i].DefultValue, "') FOR ", lst[i].ColName)
                        );
                    stbDefult.AppendLine("Go");
                }

                if (lst[i].IndexType != RowInfo.EnIndexType.Null.ToString() && lst[i].YesPrimaryKey == false)
                {
                    stbIndex.AppendLine(
                            String.Concat("IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[", strTableName, "]') AND name = N'IX_", strTableName, "_", lst[i].ColName, "')")
                     );
                    stbIndex.AppendLine(
                            String.Concat("DROP INDEX [IX_", strTableName, "_", lst[i].ColName, "] ON [dbo].[", strTableName, "] WITH ( ONLINE = OFF )")
                        );
                    stbIndex.AppendLine("Go");

                    stbIndex.AppendLine(String.Concat("CREATE NONCLUSTERED INDEX [IX_", strTableName, "_", lst[i].ColName, "] ON [dbo].[", strTableName, "] ("));
                    stbIndex.AppendLine(String.Concat(" [", lst[i].ColName, "] ", lst[i].IndexType.ToString(), " "));
                    stbIndex.AppendLine(") WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]");
                    stbIndex.AppendLine("Go");
                }

            }
            stbSql.Remove(stbSql.Length - 3, 1);
            stbSql.AppendLine(String.Concat(
                            " CONSTRAINT [PK_", strTableName, "_", strPrimaryKey, "] PRIMARY KEY CLUSTERED ",
                            "(",
                            "	[", strPrimaryKey, "] ", strIndexType,
                            ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]",
                            ") ON [PRIMARY]"
                            ));
            stbSql.AppendLine(" Go");

            //添加表的注释
            stbDescription.AppendLine(
                String.Concat("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'", strTableCaption, "' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'", strTableName, "'")
             );

            stbSql.AppendLine(stbDescription.ToString());//字段注释
            stbSql.AppendLine(stbDefult.ToString());//默认值
            stbSql.AppendLine(stbIndex.ToString());//索引
            txtCreateTableSql.Text = stbSql.ToString(); ;

            //Clipboard.SetText(txtCreateTableSql.Text);
        }

        /// <summary>根据表格结构生成待执行的SQLList </summary>
        private void GetSqlList()
        {
            String strPrimaryKey = "Code";
            String strTableName;
            String strTableCaption;
            String strIndexType = "　DESC";


            strTableName = txtTableName.Text;
            strTableCaption = txtTableCaption.Text;


            List<String> lstSql = new System.Collections.Generic.List<string>();
            List<String> lstDescription = new System.Collections.Generic.List<string>();
            List<String> lstIndex = new System.Collections.Generic.List<string>();
            List<String> lstDefult = new System.Collections.Generic.List<string>();

            System.Text.StringBuilder stbDefult = new StringBuilder();//用于存入生成默认值的语句
            System.Text.StringBuilder stbIndex = new StringBuilder();//用于存入生成默认值的语句


            System.Text.StringBuilder stbSql = new StringBuilder();
            //----删除原对象-------
            stbSql.AppendLine("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo." + strTableName + "') AND type in (N'U'))");
            stbSql.AppendLine("DROP TABLE dbo." + strTableName);
            lstSql.Add(stbSql.ToString());
            stbSql.Clear();


            //----创建新表-------
            stbSql.AppendLine("CREATE TABLE [" + strTableName + "](");

            List<RowInfo> lst = (List<RowInfo>)fpSTableInfo.DataSource;


            Int32 length = lst.Count;
            for (int i = 0; i < length; i++)
            {
                stbSql.AppendLine(String.Concat("[",
                    lst[i].ColName, "] ", lst[i].ColType, " ", lst[i].EnableNull == true ? "NULL," : "NOT NULL,"));
                if (lst[i].YesPrimaryKey == true) { strPrimaryKey = lst[i].ColName; strIndexType = lst[i].IndexType.ToString(); }


                //字段注释
                lstDescription.Add("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'" + lst[i].ColCaption
                    + "' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'" + strTableName
                    + "', @level2type=N'COLUMN',@level2name=N'" + lst[i].ColName + "'");

                //字段默认值
                if (String.IsNullOrEmpty(lst[i].DefultValue) == false)
                {
                    lstDefult.Add(
                        String.Concat("ALTER TABLE dbo.", strTableName, " ADD  CONSTRAINT DF_", strTableName, "_", lst[i].ColName, "  DEFAULT (N'", lst[i].DefultValue, "') FOR ", lst[i].ColName)
                        );
                }

                //索引
                if (lst[i].IndexType != RowInfo.EnIndexType.Null.ToString() && lst[i].YesPrimaryKey == false)
                {
                    lstIndex.Add(
                            String.Concat("IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[", strTableName, "]') AND name = N'IX_", strTableName, "_", lst[i].ColName, "')")
                            + String.Concat("DROP INDEX [IX_", strTableName, "_", lst[i].ColName, "] ON [dbo].[", strTableName, "] WITH ( ONLINE = OFF )")
                        );

                    lstIndex.Add(
                        String.Concat("CREATE NONCLUSTERED INDEX [IX_", strTableName, "_", lst[i].ColName, "] ON [dbo].[", strTableName, "] (")
                        + String.Concat(" [", lst[i].ColName, "] ", lst[i].IndexType.ToString(), " ")
                        + ") WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]"
                        );
                }
            }
            stbSql.Remove(stbSql.Length - 3, 1);
            stbSql.AppendLine(String.Concat(
                            " CONSTRAINT [PK_", strTableName, "_", strPrimaryKey, "] PRIMARY KEY CLUSTERED ",
                            "(",
                            "	[", strPrimaryKey, "] ", strIndexType,
                            ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]",
                            ") ON [PRIMARY]"
                            ));
            lstSql.Add(stbSql.ToString());
            stbSql.Clear();

            //添加表的注释
            lstDescription.Add(
                String.Concat("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'", strTableCaption, "' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'", strTableName, "'")
             );

            lstSql.AddRange(lstDescription);
            lstSql.AddRange(lstDefult);
            lstSql.AddRange(lstIndex);
            lsbSqlStr.DataSource = lstSql;

            if (chkAutoRunSql.Checked == true) { ExeCreateTableSqlList(); }
        }


        /// <summary>对数据库操作，生成新表，执行待执行的sqlList。 </summary>
        private void ExeCreateTableSqlList()
        {
            try
            {
                if (combDBName.SelectedIndex < 0) { MessageBox.Show("选择操作的数据库"); return; }
                if (lsbSqlStr.Items.Count < 0) { MessageBox.Show("没有待执行的SQL语句"); return; }
                ExeSqlStrS(lsbSqlStr.DataSource as List<String>);
                txtTableCaption.SelectAll();
                txtTableName.SelectAll();
                txtColCaptionS.SelectAll();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        

        #endregion


        
        #region 工具栏相关事件
        /// <summary>获取指定服务器上的数据库列表 </summary>
        /// <param name="dsn"></param>
        /// <param name="uid"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public List<String> GetMssqlDbNameList(string dsn, string uid, string pwd)
        {
            List<String> dbnamelist = new List<String>();
            try
            {
                

                SqlConnection conn = new SqlConnection(String.Format("data source={0};initial catalog=master;user id={1};pwd={2}", dsn, uid, pwd));
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter("select name from Master..SysDatabases order by name", conn);
                adapter.Fill(dt);

                //int i = 0;
                foreach (DataRow row in dt.Rows)
                {
                    dbnamelist.Add(row["name"].ToString());
                }
                return dbnamelist;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return dbnamelist;
            }



        }

        private void bntGetDbName_Click(object sender, EventArgs e)
        {
            combDBName.DataSource = GetMssqlDbNameList(combIP.Text, txtUser.Text, txtPassWord.Text);
            if (combDBName.Items.Count > 0) 
            {
                combDBName.SelectedIndex = 0;
                String strNewIP = combIP.Text;
                if (combIP.Items.Contains(strNewIP) == false) { combIP.Items.Insert(0, strNewIP); }
            }

            

        }


        /// <summary> 窗体保持到最前 </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void chkTopMost_CheckedChanged(object sender, EventArgs e)
        {
            this.TopMost = chkTopMost.Checked;
        }

        /// <summary> 选定的数据库发生变化 </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void combDBName_SelectedIndexChanged(object sender, EventArgs e)
        {
            LoadTableInfo();
            LoadFieldInfo();

        }




        #endregion

        #region tab2相关事件

        int intCleartFlag = 0;
        /// <summary>自动清理文框 </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void timCleartxtDbInfo_Tick(object sender, EventArgs e)
        {

            if (chkTimClear.Checked == true )
            {
                if (intCleartFlag >= Convert.ToInt32( numDUTimInterVal.Value))
                {
                    this.bntClearText_Click(null, null);
                }
                else
                {
                    intCleartFlag++;
                }
            }
            proClearText.Maximum = Convert.ToInt32(numDUTimInterVal.Value);
            proClearText.Value = Convert.ToInt32(numDUTimInterVal.Value) - intCleartFlag;

        }



        #region 右侧表信息




        private void FpsTableS_CellDoubleClick(object sender, CellClickEventArgs e)
        {
            String strInvtead = chkAutoAddInvertedComma.Checked == true ? "\"" : "";
            if (String.IsNullOrEmpty(txtDbInfo.Text.Trim()) == true)
            {
                txtDbInfo.Text = txtDbInfo.Text + strInvtead + FpsTableS.Sheets[0].ActiveCell.Text + strInvtead;
            }
            else
            {
                txtDbInfo.Text = txtDbInfo.Text + "," + strInvtead + FpsTableS.Sheets[0].ActiveCell.Text + strInvtead;

               
            }
        }

        private void FpsFiels_CellDoubleClick(object sender, CellClickEventArgs e)
        {
            String strInvtead = chkAutoAddInvertedComma.Checked == true ? "\"" : "";

            if (String.IsNullOrEmpty(txtDbInfo.Text.Trim()) == true)
            {
                txtDbInfo.Text = txtDbInfo.Text + strInvtead + FpsFields.Sheets[0].ActiveCell.Text + strInvtead;
            }
            else
            {
                txtDbInfo.Text = txtDbInfo.Text + "," + strInvtead + FpsFields.Sheets[0].ActiveCell.Text + strInvtead;
            }
        }


        private void txtDbInfo_TextChanged(object sender, EventArgs e)
        {
            if (chkAutoIntoClipboard.Checked == true)
            {
                if (String.IsNullOrEmpty(txtDbInfo.Text.Trim()) == false)
                {
                    timCleartxtDbInfo.Enabled = false;//每交变更后，都从新计算时间
                    Clipboard.SetText(txtDbInfo.Text);
                    timCleartxtDbInfo.Enabled = true;
                }
                intCleartFlag = 0;//自动清空计时标志重置为0，重新计算清空时间
            }
        }

        private void FpsTableS_EnterCell(object sender, EnterCellEventArgs e)
        {
            LoadFieldInfo();
            LoadTableData();


        }



        /// <summary> 删除FpsTabelData选中的数据 </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void menuDelete_Click(object sender, EventArgs e)
        {
            List<int> lstRowIndex = this.GetFpsSelectRowIndexS(FpsTabelData);
            int length = lstRowIndex.Count();
            if (length > 1) 
            {
                if (DialogResult.No == MessageBox.Show("是否确认删除?", this.Text, MessageBoxButtons.YesNo)) { return; }
            }
            String strTableName = FpsTableS.Sheets[0].Cells[FpsTableS.Sheets[0].ActiveRow.Index, 0].Text;
            String strCodeFieldName=FpsFields.Sheets[0].Cells[0,2].Text;
            String strSql="";
            List<String> lstSql = new List<string>();
            for (int i = 0; i < length; i++) 
            {
                strSql = "Delete " + strTableName + " Where " + strCodeFieldName + "='" + FpsTabelData.Sheets[0].Cells[lstRowIndex[i], 0].Text + "'";
                //MessageBox.Show(strSql);
                lstSql.Add(strSql);
            }
            this.ExeSqlStrS(lstSql);
            this.LoadTableData();
            

        }

        /// <summary>修改并保存FpsTabelData数据</summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void FpsTabelData_EditModeOff(object sender, EventArgs e)
        {
            String strSql = " Update " + FpsTableS.Sheets[0].Cells[FpsTableS.Sheets[0].ActiveRowIndex, 0].Text + " Set ";
            int length = FpsFields.Sheets[0].RowCount;
            for (int i = 0; i < length; i++)
            {
                if (FpsFields.Sheets[0].Cells[i, 11].Text.ToUpper() == "N")
                {
                    strSql = strSql + " " + FpsFields.Sheets[0].Cells[i, 2].Text + "="
                        + PubStCls.GetCabinet(FpsFields.Sheets[0].Cells[i, 6].Text)
                            + FpsTabelData.Sheets[0].Cells[FpsTabelData.Sheets[0].ActiveRowIndex, i].Text
                        + PubStCls.GetCabinet(FpsFields.Sheets[0].Cells[i, 6].Text) + ",";
                }
            }
            strSql = strSql.Substring(0, strSql.Length - 1);
            //添加Where条件
            strSql = strSql + " Where " + FpsFields.Sheets[0].Cells[0, 2].Text + "="
                 + PubStCls.GetCabinet(FpsFields.Sheets[0].Cells[0, 6].Text)
                 + FpsTabelData.Sheets[0].Cells[FpsTabelData.Sheets[0].ActiveRowIndex, 0].Text
                 + PubStCls.GetCabinet(FpsFields.Sheets[0].Cells[0, 6].Text);

            //MessageBox.Show(strSql);
            this.ExeSqlStr(strSql, false);

        }


        /// <summary>获得Insert语句模板 </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void bntGetInsert_Click(object sender, EventArgs e)
        {

            String strSql = "INSERT " + FpsTableS.Sheets[0].Cells[FpsTableS.Sheets[0].ActiveRowIndex, 0].Text + " (" + Environment.NewLine;
            String strfieldName = "";
            String strValues = "";
            int length = FpsFields.Sheets[0].Rows.Count;
            String strComma = "";
            for (int i = 0; i < length; i++)
            {
                strfieldName = strfieldName + strComma + FpsFields.Sheets[0].Cells[i, 2].Text;
                strValues = strValues + strComma + "'AAAAA'";
                if (i == 0) { strComma = ","; }
                if ((i + 1) % 3 == 0 && (i + 1) != length)//每3个字段添加一个回车
                {
                    strfieldName = strfieldName + Environment.NewLine;
                    strValues = strValues + Environment.NewLine;
                }
            }

            strSql = strSql + strfieldName + Environment.NewLine + ") Values (" + Environment.NewLine + strValues + Environment.NewLine + ")";
            strSql = strSql.Replace(Environment.NewLine, "\"" + Environment.NewLine + "                              ,\"");
            strSql = "String.Concat(\"" + strSql + "\");";
            strSql = strSql.Replace("AAAAA", "\",AAAAA,\"");
            strSql = lstValueName.SelectedItem.ToString() + " = " + strSql;
            txtDbInfo.Text = strSql;

        }

        /// <summary> 获取SelectSQL语句 </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void BntGetSelect_Click(object sender, EventArgs e)
        {
            txtDbInfo.Text = this.GetSelectSqlStr(chkSelectAddChina.Checked);
        }

        /// <summary>获取选表table的Select语句 </summary>
        /// <returns></returns>
        private String GetSelectSqlStr(Boolean AddchinaCaption)
        {
            String strSql = " Select ";
            String strCaption = "";
            int length = FpsFields.Sheets[0].Rows.Count;
            if (length < 1) { return ""; }
            length = FpsFields.Sheets[0].SelectionCount;
            if (length == 0)//如果没有选择块则生成全部
            {
                length = FpsFields.Sheets[0].Rows.Count;
                for (int i = 0; i < length; i++)
                {
                    if (chkSelectAddTableName.Checked == true) { strSql = strSql + FpsFields.Sheets[0].Cells[i, 1].Text + "."; }
                    strSql = strSql + FpsFields.Sheets[0].Cells[i, 2].Text;
                    if (AddchinaCaption == true) 
                    {
                        strCaption = FpsFields.Sheets[0].Cells[i, 3].Text;
                        if (strCaption.IndexOf("_") > 0) { strCaption = strCaption.Substring(0, strCaption.IndexOf("_"));}
                        if (this.IsNumeric(strCaption.Substring(0, 1)) == true) { strCaption = "[" + strCaption + "]"; }
                        strSql = strSql + " As [" + strCaption+"]";
                    }
                    strSql = strSql + ", ";
                }
            }
            else
            {//如果有选择则按选择生成
                for (int i = 0; i < length; i++)
                {

                    FarPoint.Win.Spread.Model.CellRange cellRange = FpsFields.ActiveSheet.GetSelection(i);
                    for (int j = cellRange.Row; j < cellRange.Row + cellRange.RowCount; j++)
                    {
                        if (chkSelectAddTableName.Checked == true) { strSql = strSql + FpsFields.Sheets[0].Cells[j, 1].Text + "."; }
                        strSql = strSql + FpsFields.Sheets[0].Cells[j, 2].Text;
                        if (AddchinaCaption == true) 
                        { 
                            strCaption = FpsFields.Sheets[0].Cells[j, 3].Text;
                            if (strCaption.IndexOf("_") > 0) { strCaption = strCaption.Substring(0, strCaption.IndexOf("_")); }
                            if (this.IsNumeric(strCaption.Substring(0, 1)) == true) { strCaption = "[" + strCaption + "]"; }
                            strSql = strSql + " As " + strCaption;
                        }
                        strSql = strSql + ", ";
                    }
                }
            }
            strSql = strSql.TrimEnd();
            strSql = strSql.Remove(strSql.Length - 1);
            strSql = strSql + " From " + FpsFields.Sheets[0].Cells[0, 1].Text;
            return strSql;
        }

        /// <summary>选择实体类生保存的目录</summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void bntFolder_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog fol = new FolderBrowserDialog();
            if (DialogResult.OK == fol.ShowDialog(this)) { txtFolder.Text = fol.SelectedPath; };
        }


        /// <summary>删除选订表的数据 </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void bntDelAll_Click(object sender, EventArgs e)
        {

            int length = FpsTableS.Sheets[0].Rows.Count;
            if (length < 1) { return; }
            if (DialogResult.No == MessageBox.Show("确认执行清除操作?", "SqlServerTools", MessageBoxButtons.YesNo)) { return; }
            length = FpsTableS.Sheets[0].SelectionCount;
            String sqlStr = "";
            List<String> lstSql = new List<string>();

            if (length == 0)//如果没有选择块则生成全部
            {
                length = FpsTableS.Sheets[0].Rows.Count;
                for (int i = 0; i < length; i++)
                {
                    sqlStr = "Delete " + FpsTableS.Sheets[0].Cells[i, 0].Text;
                    lstSql.Add(sqlStr);
                }
            }
            else
            {//如果有选择则按选择生成
                for (int i = 0; i < FpsTableS.Sheets[0].SelectionCount; i++)
                {
                    FarPoint.Win.Spread.Model.CellRange cellRange = FpsTableS.Sheets[0].GetSelection(i);
                    for (int j = cellRange.Row; j < cellRange.Row + cellRange.RowCount; j++)
                    {
                        sqlStr = "Delete " + FpsTableS.Sheets[0].Cells[j, 0].Text;
                        lstSql.Add(sqlStr);
                    }
                }
            }
            this.ExeSqlStrS(lstSql);
            this.LoadTableData();
        }

        /// <summary>初始化初始化Bs_no表 </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void bntInitCode_Click(object sender, EventArgs e)
        {

            if (FpsTableS.Sheets[0].Rows.Count < 1) { return; }

            String strSql="Select no_name From Bs_no";
            DataTable dtBs_no = this.GetDataTable(strSql);
            int intAddTotal = 0;
            foreach (Row row in FpsTableS.Sheets[0].Rows)
            {
                if (dtBs_no.Select("no_name='" + FpsTableS.Sheets[0].Cells[row.Index, 0].Text + "'").Count() < 1)
                {
                    this.ExeSqlStr("Insert Bs_No (no_name,suoming) values  ('"
                        + FpsTableS.Sheets[0].Cells[row.Index, 0].Text+ "','"
                        + FpsTableS.Sheets[0].Cells[row.Index, 1].Text+"')",false);
                    intAddTotal++;
                }
            }
            //文件编号FileUpLoad
            if (dtBs_no.Select("no_name='FileUpLoad'").Count() < 1)
            {
                this.ExeSqlStr("Insert Bs_No (no_name,suoming) values  ('FileUpLoad','上传文件编号')");
                intAddTotal++;
            }

            MessageBox.Show("添加了" + intAddTotal.ToString() + "个新编号!");
        }




        /// <summary>获取表的实体类 </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void bntGetEntity_Click(object sender, EventArgs e)
        {
            this.GetEntity(FpsTableS.Sheets[0].ActiveRow.Index);
        }

        /// <summary> 批量获取表的实体类 </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void bntGetEntityS_Click(object sender, EventArgs e)
        {
            List<int> lstRowIndex = this.GetFpsSelectRowIndexS(FpsTableS);
            int length = lstRowIndex.Count();
            for (int i = 0; i < length; i++) { this.GetEntity(lstRowIndex[i]); }
        }

        /// <summary> 获取指定数据库表的实体类</summary>
        /// <param name="FpsTableSRowIndex">将要生成实体类的数据库表列表中的行</param>
        private void GetEntity(int intFpsTableSRowIndex)
        {
            String strClassName = FpsTableS.Sheets[0].Cells[intFpsTableSRowIndex, 0].Text;
            String strTableName = FpsTableS.Sheets[0].Cells[intFpsTableSRowIndex, 0].Text;

            System.Text.StringBuilder stb = new StringBuilder();
            int length = FpsFields.Sheets[0].Rows.Count;
            SheetView sheet = FpsFields.Sheets[0];

            #region 写入引用
                stb.AppendLine("using System;");
                //stb.AppendLine("using System.Collections.Generic;");
                stb.AppendLine("using System.Data;");
                //stb.AppendLine("using System.Data.SqlClient;");
                //stb.AppendLine("using HuiLanKeJi.Common;");
                stb.AppendLine();
                stb.AppendLine();
                stb.AppendLine("namespace HuiLanKeJi.Common");
                stb.AppendLine("{");
                stb.AppendLine("\t/// <summary>" + FpsTableS.Sheets[0].Cells[intFpsTableSRowIndex, 1].Text + " </summary>");
                stb.AppendLine("\tpublic class " + strClassName);
                stb.AppendLine("\t{");
                stb.AppendLine();

                stb.AppendLine("\t\tprivate DbHelper dbHelper = new DbHelper();");
                stb.AppendLine();            
            #endregion

            #region 写入初始化函数,这个函数在两个构造函数中都需要使用
                stb.AppendLine("\t\t/// <summary>将类的所有属性置为初始化状态（从数据库填充数据之前的状态） </summary>");
                stb.AppendLine("\t\tpublic void InitClass()");
                stb.AppendLine("\t\t{");
                stb.AppendLine("\t\t\tTableName=\"" + strTableName + "\";");
                //stb.AppendLine("\t\t\tConn=cls.GetConn();");
                //Type tt = typeof(String);
                for (int i = 0; i < length; i++)
                {
                    switch (sheet.Cells[i, 6].Text.ToUpper())
                    {
                        //字符、日期
                        case "SAMLLDATETIME":
                        case "DATETIME":
                            if (String.IsNullOrEmpty(sheet.Cells[i, 4].Text) == false)
                            {
                                stb.AppendLine("\t\t\t" + sheet.Cells[i, 2].Text + "=Convert.ToDateTime(\"" + new Regex("(^N'|'$)|(^'|'$)").Replace(sheet.Cells[i, 4].Text, "") + "\");");
                            }
                            else
                            {
                                stb.AppendLine("\t\t\t" + sheet.Cells[i, 2].Text + "=DateTime.Now;");
                            }
                            break;
                        case "DATE":
                            if (String.IsNullOrEmpty(sheet.Cells[i, 4].Text) == false)
                            {
                                stb.AppendLine("\t\t\t" + sheet.Cells[i, 2].Text + "=Convert.ToDateTime(\"" + new Regex("(^N'|'$)|(^'|'$)").Replace(sheet.Cells[i, 4].Text, "") + "\");");
                            }
                            else
                            {
                                stb.AppendLine("\t\t\t" + sheet.Cells[i, 2].Text + "=DateTime.Now;");
                            }
                            break;
                        case "CHAR":
                        case "NCHAR":
                        case "VARCHAR":
                        case "NVARCHAR":
                        case "TEXT":
                        case "NTEXT":
                            if (String.IsNullOrEmpty(sheet.Cells[i, 4].Text) == true)
                            {
                                stb.AppendLine("\t\t\t" + sheet.Cells[i, 2].Text + "=\"\";");
                            }
                            else
                            {
                                stb.AppendLine("\t\t\t" + sheet.Cells[i, 2].Text + "=\"" + new Regex("(^N'|'$)|(^'|'$)").Replace(sheet.Cells[i, 4].Text, "") + "\";");
                            }

                            break;
                        //数值型
                        case "NUMERIC":
                        case "MONEY":
                        case "INT":
                        case "BIGINT":
                        case "SMALLINT":
                        case "SMALLMONEY":

                            //System.Diagnostics.Debug.WriteLine(sheet.Cells[i, 3].Text);
                            if (String.IsNullOrEmpty(sheet.Cells[i, 4].Text) == true)
                            {
                                stb.AppendLine("\t\t\t" + sheet.Cells[i, 2].Text + "=0;");
                            }
                            else
                            {
                                stb.AppendLine("\t\t\t" + sheet.Cells[i, 2].Text + "=" + new Regex("(^N'|'$)|(^'|'$)").Replace(sheet.Cells[i, 4].Text, "") + ";");
                            }

                            break;
                        default://其它类型一律视为字符串
                            if (String.IsNullOrEmpty(sheet.Cells[i, 4].Text) == true)
                            {
                                stb.AppendLine("\t\t\t" + sheet.Cells[i, 2].Text + "=\"\";");
                            }
                            else
                            {
                                stb.AppendLine("\t\t\t" + sheet.Cells[i, 2].Text + "=\"" + new Regex("(^N'|'$)|(^'|'$)").Replace(sheet.Cells[i, 4].Text, "") + "\";");
                            }
                            break;
                    }

                }

                stb.AppendLine("\t\t\t" + "IsOnlyInit = true; //初始化后尚未从数据库填充时，将IsOnlyInit设为ture");
                stb.AppendLine("\t\t}");
                stb.AppendLine();
            #endregion

            #region 写入构造函数
                #region 以Code为参数的构造函数,用Code自动填充实体类

                stb.AppendLine("\t\t///<summary>写入构造函数，并设置默认值 </summary>");
                stb.AppendLine("\t\tpublic " + strClassName + "(String strCode)");
                stb.AppendLine("\t\t{");

                stb.AppendLine("\t\t\tthis.InitClass();");
                stb.AppendLine("\t\t\tCode = strCode;");
                stb.AppendLine("\t\t\tthis.ExecSelectSqlStr();");

                stb.AppendLine("\t\t}");
                stb.AppendLine();

                #endregion

                #region 无参数构造函数
                stb.AppendLine("\t\t////<summary>写入构造函数，并设置默认值 </summary>");
                stb.AppendLine("\t\tpublic " + strClassName + "()");
                stb.AppendLine("\t\t{");
                stb.AppendLine("\t\t\tthis.InitClass();");
                stb.AppendLine("\t\t}");
                stb.AppendLine();
                #endregion

            
            #endregion
            
            #region 写入析构函数
            //stb.AppendLine("\t\t///<summary>析构函数</summary>");
            //stb.AppendLine("\t\t~"+strClassName+"()");
            //stb.AppendLine("\t\t{");
            //stb.AppendLine("\t\t\tthis.Conn.Close();");
            //stb.AppendLine("\t\t\tthis.Conn.Dispose();");
            //stb.AppendLine("\t\t}");
            //stb.AppendLine();
            #endregion


            #region 写入字段属性


            stb.AppendLine("\t\t#region 字段属性属性----------------------------");

            stb.AppendLine("\t\t///<summary>表名 </summary>");
            stb.AppendLine("\t\tpublic String TableName{ get;set; }");
            stb.AppendLine();

            //stb.AppendLine("\t\tprivate SqlConnection _conn;");
            //stb.AppendLine("\t\t///<summary>数据库连接 </summary>");
            //stb.AppendLine("\t\tpublic SqlConnection Conn");
            //stb.AppendLine("\t\t{ ");
            //stb.AppendLine("\t\t\tget ");
            //stb.AppendLine("\t\t\t{ ");
            //stb.AppendLine("\t\t\t\tif (_conn == null) { _conn = cls.GetConn(); }");
            //stb.AppendLine("\t\t\t\treturn _conn;");        
            //stb.AppendLine("\t\t\t} ");
            //stb.AppendLine("\t\t\tset {_conn=value;} ");
            //stb.AppendLine("\t\t}");
            //stb.AppendLine();

            stb.AppendLine("\t\t/// <summary> 只读属性,标识类中是否已包含从数据库中填充的数据，即是否只是初始化而未读取到数据。");
            stb.AppendLine("\t\t//// 当只是初始化而未填充数据时为true。默认值为true</summary>");

            stb.AppendLine("\t\tpublic Boolean IsOnlyInit");
            stb.AppendLine("\t\t{");
            stb.AppendLine("\t\t\tget;private set;");
            stb.AppendLine("\t\t}");
            stb.AppendLine();



           // String strGetAndSet="get;set;"
            for (int i = 0; i < length; i++)
            {

                stb.AppendLine("\t\t///<summary>" + sheet.Cells[i, 3].Text + " </summary>");
                if (sheet.Cells[i, 11].Text == "Y")
                {
                    stb.AppendLine("\t\tpublic " + PubStCls.ChangeToCSharpType(sheet.Cells[i, 6].Text, Convert.ToInt32(sheet.Cells[i, 8].Text), Convert.ToInt32(sheet.Cells[i, 9].Text))
                        + " " + sheet.Cells[i, 2].Text + " { get; private set; }");//计算列只在可以赋值，以便用select填充时使用
                }
                else 
                {
                    //String strType = sheet.Cells[i, 6].Text;
                    //int strLength =Convert.ToInt32(sheet.Cells[i, 8].Text);
                    //int strDecLength= Convert.ToInt32(sheet.Cells[i, 9].Text);
                    stb.AppendLine("\t\tpublic " + PubStCls.ChangeToCSharpType(sheet.Cells[i, 6].Text, Convert.ToInt32(sheet.Cells[i, 8].Text), Convert.ToInt32(sheet.Cells[i, 9].Text))
                            + " " + sheet.Cells[i, 2].Text + " { get;set; }");
                }
                
                stb.AppendLine();
            }
            stb.AppendLine("\t\t#endregion");

            #endregion



            stb.AppendLine("\t\t#region 各种方法--------------------------------");

            #region 写入GetNewCode方法
            stb.AppendLine("\t\t/// <summary>获取一个新的Code值,并填充到Code属性中去。之所以没有code属性中自动生成，是为适应一个变量生成多条insert的情况</summary>");
            stb.AppendLine("\t\t///<returns>新获取的Code值</returns>");
            stb.AppendLine("\t\tpublic String GetNewCode()");
            stb.AppendLine("\t\t{");
            //stb.AppendLine("\t\t\tthis.Code = new clsPublic().GetCode(\"" + strTableName + "\", this.Conn);");
            stb.AppendLine("\t\t\tdbHelper.GetCode(this.TableName);");
            stb.AppendLine("\t\t\treturn this.Code;");
            stb.AppendLine("\t\t}");
            #endregion

            #region 写入GetSelectStr方法

            String strNewLine = "\"" + Environment.NewLine + "\t\t\t\t+\"";
            String strComma = "";    //逗号  


            stb.AppendLine("\t\t/// <summary>返回只有select...From的初始化select语句 </summary>");
            stb.AppendLine("\t\t/// <returns></returns>");

            stb.AppendLine("\t\tprivate String GetInitSelectSqlStr()");
            stb.AppendLine("\t\t{");

            System.Text.StringBuilder stbSelect = new StringBuilder("SELECT TOP 1 ");
            strComma = "";  
            for (int i = 0; i < length; i++)
            {
                stbSelect.Append(  strNewLine + strComma+sheet.Cells[i, 2].Text);
                if (strComma != ",") { strComma = ","; }
            }
            stbSelect.Append(" FROM " + strTableName + " ");
            stb.AppendLine("\t\t\treturn\"" + stbSelect.ToString() + "\";");
            stb.AppendLine("\t\t}");
            stb.AppendLine();




            stb.AppendLine("\t\t///<summary>获得实体类的SelectSql语句，调用此方法之前，需要先对主键值</summary>");
            stb.AppendLine("\t\t///<returns>添加到数据的Select语句</returns>");
            stb.AppendLine("\t\tpublic String GetSelectSqlStr()");
            stb.AppendLine("\t\t{");

            stb.AppendLine("\t\t\tif (String.IsNullOrEmpty(this." + sheet.Cells[0, 2].Text + ".Trim()) == true) { throw new Exception(\"主键值未为空,无法获取完整Select语句\"); }");
            stb.AppendLine("\t\t\treturn  GetInitSelectSqlStr()+\"" + " WHERE " + sheet.Cells[0, 2].Text + "= '\"+this." + sheet.Cells[0, 2].Text + ".Trim()+\"'" + "\";");
            stb.AppendLine("\t\t}");
            stb.AppendLine();

            stb.AppendLine("\t\t/// <summary>获得实体类带有指定Where条件的SelectSql语句,注意 生成的SQL语句会自动添加top 1 以便于填充实体类</summary>");
            stb.AppendLine("\t\t/// <param name=\"strWhere\">Where条件语句，注意不要加 \"Where\"</param>");
            stb.AppendLine("\t\t/// <returns>添加到数据的Select语句</returns>");
            stb.AppendLine("\t\tpublic String GetSelectSqlStr(String strWhere)");
            stb.AppendLine("\t\t{");
            stb.AppendLine("\t\t\treturn GetInitSelectSqlStr() + \" WHERE \" + strWhere;");
            stb.AppendLine("\t\t}");
            stb.AppendLine();



            #endregion

            #region 写入ExecSelectSqlStr方法,填充实体类的属性

            stb.AppendLine("\t\t/// <summary>根据Select语句填充实体类 </summary>");
            stb.AppendLine("\t\t/// <param name=\"strSelect\">需要执行的Select语句</param>");
            stb.AppendLine("\t\t/// <returns>如果Select有返回数据集则返回找到的DataRow,否则返回NULL并将实体类设置为初始化状态</returns>");
            stb.AppendLine("\t\tprivate DataRow FullEntity(String strSelect)");
            stb.AppendLine("\t\t{");
            //stb.AppendLine("\t\t\tDataTable dt=cls.GetDataTable(strSelect,this.Conn);");
            stb.AppendLine("\t\t\tDataTable dt=dbHelper.GetDataTable(strSelect);");

            stb.AppendLine("\t\t\tif (dt.Rows.Count<1){ this.InitClass(); return null;}//填充不成功,将类的状态置为初始化状态");
            stb.AppendLine("\t\t\tDataRow dr=dt.Rows[0];");

            for (int i = 0; i < length; i++)
            {
                stb.AppendLine("\t\t\tthis." + sheet.Cells[i, 2].Text+"= dr[\"" + sheet.Cells[i, 2].Text + "\"]==System.DBNull.Value ? " + PubStCls.GetDefultValue(sheet.Cells[i, 6].Text)
                    + " : (" + PubStCls.ChangeToCSharpType(sheet.Cells[i, 6].Text, Convert.ToInt32(sheet.Cells[i, 8].Text), Convert.ToInt32(sheet.Cells[i, 9].Text))
                    + ") dr[\"" + sheet.Cells[i, 2].Text + "\"] ;");
            }
            stb.AppendLine("\t\t\tthis.IsOnlyInit = false;//数据填充成功,修改状态标示");
            stb.AppendLine("\t\t\treturn dr;");
            stb.AppendLine("\t\t}");
            stb.AppendLine();
            
            stb.AppendLine("\t\t/// <summary>执行Select语句填充实体类的属性 </summary>");
            stb.AppendLine("\t\t/// <returns>返回找到记录的DataRow,如果没有找到数据则返回NULL并将类置为初始化状态</returns>");
            stb.AppendLine("\t\tpublic DataRow ExecSelectSqlStr()");
            stb.AppendLine("\t\t{");
            stb.AppendLine("\t\t\treturn this.FullEntity(this.GetSelectSqlStr());");
            stb.AppendLine("\t\t}");
            stb.AppendLine();      


            stb.AppendLine("\t\t/// <summary>执行带有指定where条件的Select语句填充实体类的属性,注意只填充找到的第一条附合where条件进行填充 </summary>");
            stb.AppendLine("\t\t/// <param name=\"strWhere\">Where条件语句，注意不要加 \"Where\"</param>");
            stb.AppendLine("\t\t/// <returns>返回找到记录的DataRow,如果没有找到数据则返回NULL并将类置为初始化状态</returns>");
            stb.AppendLine("\t\tpublic DataRow ExecSelectSqlStr(String strWhere)");
            stb.AppendLine("\t\t{");
            stb.AppendLine("\t\t\treturn this.FullEntity(this.GetSelectSqlStr(strWhere));");
            stb.AppendLine("\t\t}");
            stb.AppendLine();



            #endregion


            #region 写入GetInstrSqlStr方法

            stb.AppendLine("\t\t///<summary>获得实体类的InsertSQL语句</summary>");
            stb.AppendLine("\t\t///<returns>添加到数据的insert语句</returns>");
            stb.AppendLine("\t\tpublic String GetInstrSqlStr()");
            stb.AppendLine("\t\t{");

            //这里将来添加主键重复的有效性检验
            //当构键insert时，如果Code字段为空，则获取一个新的值并自动填充,第0行的字段为主键
            //stb.AppendLine("\t\t\tif (String.IsNullOrEmpty(this." + sheet.Cells[0, 2].Text + ".Trim())==true){ this." + sheet.Cells[0, 2].Text + " = new clsPublic().GetCode(\"" + strTableName + "\", this.Conn); }");
            stb.AppendLine("\t\t\tif (String.IsNullOrEmpty(this.Code.Trim()) == true) { this.Code = dbHelper.GetCode(this.TableName); }");

            System.Text.StringBuilder stbInsertFname = new StringBuilder("INSERT INTO " + strTableName + "(");
            System.Text.StringBuilder stbInsertValuse = new StringBuilder(" VALUES (");
            //生成SQL语句体

            strComma = "";
            for (int i = 0; i < length; i++)
            {
                if (sheet.Cells[i, 11].Text == "Y") { continue; }//用生insert语句时跳过计算列
                stbInsertFname.Append(strNewLine + strComma + sheet.Cells[i, 2].Text);//如果将来生成的要SQL语句在执行时出问题可以改为sheet.Cells[i, 0].Text ，第0列是表名.字段名格式
                stbInsertValuse.Append(strNewLine + strComma
                                            + PubStCls.GetCabinet(sheet.Cells[i, 6].Text)
                                            + "\" +  Convert.ToString(this." + sheet.Cells[i, 2].Text + ").Replace(\"'\", \"''\")+ \""
                                            + PubStCls.GetCabinet(sheet.Cells[i, 6].Text));
                System.Diagnostics.Debug.WriteLine(stbInsertValuse.ToString());
                if (strComma != ",") { strComma = ","; }

            }
            //stbInsertFname.Remove(stbInsertFname.Length - 1, 1);
            stbInsertFname.Append(strNewLine + ")");
            stbInsertValuse.Append(strNewLine + ")");
            stb.AppendLine("\t\t\treturn \"" + stbInsertFname.ToString() + strNewLine + stbInsertValuse.ToString() + "\";");//生成返回语句
            stb.AppendLine("\t\t}");

            #endregion
            #region 写入ExecInsertSQlStr方法
            stb.AppendLine("\t\t/// <summary>执行Insert语句 </summary>");
            stb.AppendLine("\t\t /// <returns>执行后影响的行数</returns>");
            stb.AppendLine("\t\tpublic Int32 ExecInsertSQlStr()");
            stb.AppendLine("\t\t{");
            //stb.AppendLine("\t\t\tSqlCommand cmd = new SqlCommand();");
            //stb.AppendLine("\t\t\tcmd.Connection = this.Conn;");
            //stb.AppendLine("\t\t\tcmd.CommandText = this.GetInstrSqlStr();");
            //stb.AppendLine("\t\t\treturn cmd.ExecuteNonQuery();");
            stb.AppendLine("\t\t\treturn dbHelper.ExecuteSqlStr(this.GetInitSelectSqlStr());");

            stb.AppendLine("\t\t}");

            #endregion

            #region 写入GetUpdateSqlStr方法

            stb.AppendLine("\t\t///<summary>获得实体类的Update语句</summary>");
            stb.AppendLine("\t\t///<returns>添加到数据的Update语句</returns>");
            stb.AppendLine("\t\tpublic String GetUpdateSqlStr()");
            stb.AppendLine("\t\t{");
            stb.AppendLine("\t\t\t\tif (String.IsNullOrEmpty(this." + sheet.Cells[0, 2].Text + ") == true)  { throw new Exception(\"执行GetUpdateSqlStr时,主键属性为空!\");  }");
            System.Text.StringBuilder stbUpdate = new StringBuilder("UPDATE  " + strTableName + " SET ");//实体类名就是表名

            strComma = "";
            for (int i = 0; i < length; i++)
            {
                if (sheet.Cells[i, 11].Text == "Y") { continue; }//用生Update语句时跳过计算列
                stbUpdate.Append(strNewLine + strComma + sheet.Cells[i, 2].Text + "=");
                stbUpdate.Append(PubStCls.GetCabinet(sheet.Cells[i, 6].Text)
                                     + "\" +  Convert.ToString(this." + sheet.Cells[i, 2].Text + ").Replace(\"'\", \"''\")+ \""
                                     + PubStCls.GetCabinet(sheet.Cells[i, 6].Text)
                    );
                if (strComma != ",") { strComma = ","; }
            }
            //去除最后一个","
            // stbUpdate.Remove(stbUpdate.Length - 1, 1);
            stbUpdate.Append(strNewLine + " WHERE " + sheet.Cells[0, 2].Text +//第0行的字段为主键
                                "='\"+ Convert.ToString(this." + sheet.Cells[0, 2].Text + ").Replace(\"'\", \"''\")+ \"'");//我操！看着有点眼花~~佩服一下自已！

            stb.AppendLine("\t\t\treturn \"" + stbUpdate.ToString() + "\";");//生成返回语句
            stb.AppendLine("\t\t}");


            #endregion

            #region 写入ExecUpdateSqlStr方法
            stb.AppendLine("\t\t/// <summary>执行Update语句 </summary>");
            stb.AppendLine("\t\t/// <returns>执行后影响的行数</returns>");
            stb.AppendLine("\t\tpublic Int32 ExecUpdateSQlStr()");
            stb.AppendLine("\t\t {");
            //stb.AppendLine("\t\t\tSqlCommand cmd = new SqlCommand();");
            //stb.AppendLine("\t\t\tcmd.Connection = this.Conn;");
            //stb.AppendLine("\t\t\tcmd.CommandText = this.GetUpdateSqlStr();");
            //stb.AppendLine("\t\t\treturn cmd.ExecuteNonQuery();");
            stb.AppendLine("\t\t\treturn dbHelper.ExecuteSqlStr(this.GetUpdateSqlStr());");
            stb.AppendLine("\t\t }");

            #endregion



            #region 写入GetDeleteSqlStr方法

            stb.AppendLine("\t\t///<summary>获得实体类的Delete语句</summary>");
            stb.AppendLine("\t\t///<returns>添加到数据的Delete语句</returns>");
            stb.AppendLine("\t\tpublic String GetDeleteSqlStr()");
            stb.AppendLine("\t\t{");
            System.Text.StringBuilder stbDelete = new StringBuilder();
            stb.AppendLine("\t\tif (String.IsNullOrEmpty(this." + sheet.Cells[0, 2].Text + ") == true)  { throw new Exception(\"执行GetDeleteSqlStr时,主键属性为空!\");  }");
            stbDelete.Append("DELETE FROM " + strTableName + " WHERE " + sheet.Cells[0, 2].Text + "='\"+this." + sheet.Cells[0, 2].Text + "+\"'");
            stb.AppendLine("\t\treturn \"" + stbDelete.ToString() + "\";");//生成返回语句
            stb.AppendLine("\t\t}");


            #endregion
            #region 写入ExecDeleteSQlStr方法
            stb.AppendLine("\t\t/// <summary>执行Delete语句 </summary>");
            stb.AppendLine("\t\t/// <returns>执行后影响的行数</returns>");
            stb.AppendLine("\t\tpublic Int32 ExecDeleteSQlStr()");
            stb.AppendLine("\t\t {");
            //stb.AppendLine("\t\t\tSqlCommand cmd = new SqlCommand();");
            //stb.AppendLine("\t\t\tcmd.Connection = this.Conn;");
            //stb.AppendLine("\t\t\tcmd.CommandText = this.GetDeleteSqlStr();");
            //stb.AppendLine("\t\t\treturn cmd.ExecuteNonQuery();");
            stb.AppendLine("\t\t\treturn dbHelper.ExecuteSqlStr(this.GetDeleteSqlStr());");

            stb.AppendLine("\t\t }");
            #endregion


            stb.AppendLine("\t\t#endregion");

            stb.AppendLine("\t}");
            stb.AppendLine("}");
            txtDbInfo.Text = stb.ToString();


            #region 自动保存到指定文件夹子中
            if (String.IsNullOrEmpty(txtFolder.Text.Trim()) == true) { this.bntFolder_Click(null, null); }
            String strClassFileName = strClassName + ".cs";
            PubStCls.SavaFile(txtDbInfo.Text, txtFolder.Text, strClassFileName);
            #endregion
        }

        private void bntGetDataInsert_Click(object sender, EventArgs e)
        {
            MessageBox.Show("根据数据生成insert语句,待实现");
            return;

            //String strSql = "INSERT " + FpsTableS.Sheets[0].Cells[FpsTableS.Sheets[0].ActiveRowIndex, 0].Text + " (" + Environment.NewLine;
            //String strfieldName = "";
            //String strValues = "";
            //int length = FpsFiels.Sheets[0].Rows.Count;
            //String strComma = "";
            //for (int i = 0; i < length; i++)
            //{
            //    strfieldName = strfieldName + strComma + FpsFiels.Sheets[0].Cells[i, 2].Text;
            //    strValues = strValues + strComma + "'AAAAA'";
            //    if (i == 0) { strComma = ","; }
            //    if ((i + 1) % 3 == 0 && (i + 1) != length)//每3个字段添加一个回车
            //    {
            //        strfieldName = strfieldName + Environment.NewLine;
            //        strValues = strValues + Environment.NewLine;
            //    }
            //}

            //strSql = strSql + strfieldName + Environment.NewLine + ") Values (" + Environment.NewLine + strValues + Environment.NewLine + ")";
            //strSql = strSql.Replace(Environment.NewLine, "\"" + Environment.NewLine + "                              ,\"");
            //strSql = "String.Concat(\"" + strSql + "\");";
            //strSql = strSql.Replace("AAAAA", "\",AAAAA,\"");
            //strSql = lstValueName.SelectedItem.ToString() + " = " + strSql;
            //txtDbInfo.Text = strSql;
        }



        private void bntGetNameFunc_Click(object sender, EventArgs e)
        {

            List<String> lstSql = new List<String>();

            List<int> lstRowIndex = this.GetFpsSelectRowIndexS(FpsTableS);
            int length = lstRowIndex.Count();

            System.Text.StringBuilder sbSql = new StringBuilder();
            txtDbInfo.Text = "";
            for (int i = 0; i < length; i++)
            {
                List<String> lstTemp = this.GetNameFuncString(lstRowIndex[i]);
                lstTemp.ForEach((lstItem) => { txtDbInfo.Text = txtDbInfo.Text + lstItem + Environment.NewLine + " GO " + Environment.NewLine; });
                lstSql.AddRange(lstTemp);
            }
            if (ChkAutoRunNameFunc.Checked == true) { this.ExeSqlStrS(lstSql); }    //txtDbInfo.Text = sbSql.ToString();



        }

        /// <summary>获取选定fpstable表格指定的获取名姓函数的SQL语句 </summary>
        /// <param name="intFpsTableSRowIndex">指定的行号</param>
        /// <returns>SQL命令</returns>
        private List<String> GetNameFuncString(int intFpsTableSRowIndex)
        {

            List<String> lstSQl = new List<string>();

            String strTableName = FpsTableS.Sheets[0].Cells[intFpsTableSRowIndex, 0].Text;

            String strFuncName = strTableName;//如果以info结尾,则去除,这样做的的目同是为了让函数名看起来更合理.
            if (strFuncName.Substring(strFuncName.Length - "Info".Length).ToUpper() == "INFO") { strFuncName = strFuncName.Substring(0, strFuncName.Length - "Info".Length); }
            strFuncName = "Get" + strFuncName + "Name";//将生成的函数名

            String strTableDescription = FpsTableS.Sheets[0].Cells[intFpsTableSRowIndex, 1].Text;
            int intInfolength = "基础信息".Length;//如是以基础信息或信息结尾,则去除
            int intTableDescriptionLenght = strTableDescription.Length;

            if (intTableDescriptionLenght > intInfolength)
            {
                if (strTableDescription.Substring(intTableDescriptionLenght - intInfolength, intInfolength) == "基础信息")
                { strTableDescription = strTableDescription.Substring(0, intTableDescriptionLenght - intInfolength); }
            }
            else
            {
                intInfolength = "信息".Length;
                if (intTableDescriptionLenght > intInfolength)
                {
                    if (strTableDescription.Substring(intTableDescriptionLenght - intInfolength, intInfolength) == "信息")
                    { strTableDescription = strTableDescription.Substring(0, intTableDescriptionLenght - intInfolength); }
                }
            }

            String strDescription = "获取指定编号的" + strTableDescription + "名称/姓名";//将成生成的函数说明

            //得到防错语句
            String strSql =
           "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + strFuncName + "]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[" + strFuncName + "]";
            lstSQl.Add(strSql);
            //得到函数体
            strSql = String.Concat(
                  "-- =============================================", Environment.NewLine
                 , "-- Author: 吴珂", Environment.NewLine
                 , "-- Create date: ", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"), Environment.NewLine
                 , "-- Description: ", strDescription, Environment.NewLine
                 , "-- =============================================", Environment.NewLine
                 , "CREATE FUNCTION ", strFuncName, Environment.NewLine
                 , "(", Environment.NewLine
                 , "	--", strTableDescription, "编号", Environment.NewLine
                 , "	@Code NVARCHAR(15)", Environment.NewLine
                 , ")", Environment.NewLine
                 , "RETURNS NVARCHAR(50)", Environment.NewLine
                 , "AS", Environment.NewLine
                 , "BEGIN", Environment.NewLine
                 , "	--", strTableDescription, "名称/姓名", Environment.NewLine
                 , "	DECLARE @ResultVar NVARCHAR(50)", Environment.NewLine
                 , "	SELECT @ResultVar=ISNULL(Name,@Code) FROM ", strTableName, " WHERE Code=@Code", Environment.NewLine
                 , "	RETURN @ResultVar", Environment.NewLine
                 , "", Environment.NewLine
                 , "END", Environment.NewLine
            );
            lstSQl.Add(strSql);

            //得到函数说明
            strSql = "EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'" + strDescription
                + "' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'" + strFuncName + "'";
            lstSQl.Add(strSql);





            return lstSQl;

        }




        private void butGetPinYinTrigger_Click(object sender, EventArgs e)
        {
            List<String> lstSql = new List<String>();

            List<int> lstRowIndex = this.GetFpsSelectRowIndexS(FpsTableS);
            int length = lstRowIndex.Count();

            System.Text.StringBuilder sbSql = new StringBuilder();
            txtDbInfo.Text = "";
            for (int i = 0; i < length; i++)
            {
                List<String> lstTemp = this.GetPinYinTriggerString(lstRowIndex[i]);
                lstTemp.ForEach((lstItem) => { txtDbInfo.Text = txtDbInfo.Text + lstItem + Environment.NewLine + " GO " + Environment.NewLine; });
                lstSql.AddRange(lstTemp);
            }
            if (chkAutoRunPinYinTrigger.Checked == true) { this.ExeSqlStrS(lstSql); }    //txtDbInfo.Text = sbSql.ToString();

        }

        /// <summary>获取fpstable表格中选中行的生成拼音触发器 </summary>
        /// <param name="intFpsTableSRowIndex">指定的行号</param>
        /// <returns>SQL命令List</returns>
        private List<String> GetPinYinTriggerString(int intFpsTableSRowIndex)
        {
            List<string> lstSQl = new List<string>();

            String strTableDescription = FpsTableS.Sheets[0].Cells[intFpsTableSRowIndex, 1].Text;
            String strTableName = FpsTableS.Sheets[0].Cells[intFpsTableSRowIndex, 0].Text;
            String strTriggerName = strTableName+ "_Insert_Update";


            String strSql = "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + strTriggerName + "]') AND type in (N'TR')) DROP FUNCTION [dbo].[" + strTriggerName + "]";
            lstSQl.Add(strSql);


            strSql = String.Concat("--", strTableDescription , "[",strTableName,"]触发器", Environment.NewLine
                              , "-- =============================================", Environment.NewLine
                              , "-- Author:吴珂", Environment.NewLine
                              , "-- Create date:" ,DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"), Environment.NewLine
                              , "-- Description: 如果拼音为空则自动根据名称生成拼音", Environment.NewLine
                              , "-- =============================================", Environment.NewLine
                              , "CREATE TRIGGER dbo.", strTableName, "_Insert_Update", Environment.NewLine
                              , "   ON  dbo.",strTableName, Environment.NewLine
                              , "   AFTER INSERT,UPDATE", Environment.NewLine
                              , "AS ", Environment.NewLine
                              , "BEGIN", Environment.NewLine
                              , "	SET NOCOUNT ON;", Environment.NewLine
                              , "	DECLARE @Code NVARCHAR(15),@Name NVARCHAR(50),@PinYin NVARCHAR(50)", Environment.NewLine
                              , "	SELECT @Code=Code,@Name=Name, @PinYin=Pinyin FROM inserted", Environment.NewLine
                              , "	IF ISNULL(@PinYin,'')=''", Environment.NewLine
                              , "		BEGIN", Environment.NewLine
                              , "			UPDATE ",strTableName," SET PinYin=dbo.GetPinYin(@Name) WHERE Code=@Code", Environment.NewLine
                              , "		END", Environment.NewLine
                              , "    -- Insert statements for trigger here", Environment.NewLine
                              , "", Environment.NewLine
                              , "END");
            lstSQl.Add(strSql);
            return lstSQl;

        }



        /// <summary>获取指定数据库的所有用户表信息 </summary>
        private void LoadTableInfo()
        {
            String strSql = String.Concat("SELECT distinct  ",
                "        表名=d.name, ",
                "        表说明=isnull(f.value,'')",
                "        FROM   sysobjects d",
                "        left  join   sys.extended_properties f   on   d.id=f.major_id   and   f.minor_id   =0  ",
                "        where   d.xtype='U'   and     d.name<>'dtproperties' and d.name<>'Bs_no' Order By isnull(f.value,'') desc");
            //DataTable dt=this.gett
            FpsTableS.DataSource = this.GetDataTable(strSql);
            FpsTableS.Sheets[0].Columns[0].Width = 100;
            FpsTableS.Sheets[0].Columns[1].Width = 100;
            foreach (FarPoint.Win.Spread.Row row in FpsTableS.Sheets[0].Rows) { row.Height = 30; }
        }

        /// <summary>获取字段信息 </summary>
        private void LoadFieldInfo()
        {

            String strSql = String.Concat("SELECT  UPPER(d.name +'.'+a.name) AS MainKey,   ",
                   "表名=d.name,   ",
                   "字段名=a.name,   ",
                   "说明=isnull(g.value,''),   ",
                   "默认值=replace(replace(isnull(e.text,''),')',''),'(',''),   ",
                   "键=case   when   exists(SELECT   1   FROM   sysobjects   where   xtype='PK'   and   name   in   (   ",
                   "SELECT   name   FROM   sysindexes   WHERE   indid   in(   ",
                   "SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid   ",
                   ")))   then   '√'   else   ''   end,   ",
                   "类型=b.name,   ",
                   "字节=a.length,   ",
                   "长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),   ",
                   "小数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),   ",
                   "可空=case   when   a.isnullable=1   then   '√'else   ''   end,   ",
                   "计算列=case when a.iscomputed=1 then 'Y' else 'N' end ",
                   "FROM   syscolumns   a   ",
                   "left   join   systypes   b   on   a.xtype=b.xusertype   ",
                   "inner   join   sysobjects   d   on   a.id=d.id     and   d.xtype='U'   and     d.name<>'dtproperties'   ",
                   "left   join   syscomments   e   on   a.cdefault=e.id   ",
                   "left   join   sys.extended_properties g   on   a.id=g.major_id   and   a.colid=g.minor_id          ",
                   "left   join   sys.extended_properties f   on   d.id=f.major_id   and   f.minor_id   =0   ",
                   "where   d.name='", FpsTableS.Sheets[0].Cells[FpsTableS.Sheets[0].ActiveRow.Index, 0].Text, "'         ",
                   "order   by   a.id,a.colorder");
            FpsFields.DataSource = GetDataTable(strSql);

            int[] intWidth = new int[] { 20, 80, 100, 136,100, 30, 60, 36, 36, 36, 36 };
            int length = intWidth.Length;
            for (int i = 0; i < length; i++)
            {
                FpsFields.Sheets[0].Columns[i].Width = intWidth[i];
            }
            foreach (Row row in FpsFields.Sheets[0].Rows) { row.Height = 30; }

        }

        /// <summary>读取FpsTableS选定行的数据并显示在FpsTabelData表中 </summary>
        private void LoadTableData()
        {
            try
            {
                FpsFields.Sheets[0].ClearSelection();
                String strSql = this.GetSelectSqlStr(true);
                strSql = strSql.ToUpper().Replace("SELECT", "SELECT Top 500");
                FpsTabelData.DataSource = this.GetDataTable(strSql);
                if (FpsFields.Sheets[0].Rows.Count > 0)
                {
                    int length = FpsFields.Sheets[0].Rows.Count;
                    for (int i = 0; i < length; i++)
                    {
                        if (FpsFields.Sheets[0].Cells[i, 11].Text.ToUpper() == "Y") 
                        { 
                            //FpsTabelData.Sheets[0].Columns[i].Locked = true;
                            //改变背景色,看一下 color的使用方法
                            //Color col = new Color();
                           // FpsTabelData.Sheets[0].Columns[i].BackColor=col;
                         }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        /// <summary>获取指定FpSpread表格中需要求处理的行的序号 </summary>
        /// <returns></returns>
        private List<Int32>  GetFpsSelectRowIndexS(FpSpread fps)
        { 
            List<int> lstRowIndexxS=new List<int>();


            int length = fps.Sheets[0].Rows.Count;
            if (length < 1) { return lstRowIndexxS; }
            length = fps.Sheets[0].SelectionCount;
            if (length == 0)//如果没有选择块则生成全部
            {
                length = fps.Sheets[0].Rows.Count;
                for (int i = 0; i < length; i++) {lstRowIndexxS.Add(i); }
            }
            else
            {//如果有选择则按选择生成
                for (int i = 0; i < fps.Sheets[0].SelectionCount; i++)
                {
                    FarPoint.Win.Spread.Model.CellRange cellRange = fps.Sheets[0].GetSelection(i);
                    for (int j = cellRange.Row; j < cellRange.Row + cellRange.RowCount; j++) { lstRowIndexxS.Add(j); }
                }
            }

            return lstRowIndexxS;
        }

        #endregion

        #region 左侧文本框相关信息


        #region 生成SQL数组

        private void txtSqlS_Click(object sender, EventArgs e)
        {
            String strTemp;
            String[] strRel = new String[] { "", "", "", "" };

            if (String.IsNullOrEmpty(txtSqlS.Text.Trim()) == false) { return; }
            txtSqlS.Text = Clipboard.GetText().Trim();
            strTemp = Clipboard.GetText().Trim();

            strTemp = strTemp.Replace(Environment.NewLine, "");

            strTemp = new Regex(@"FROM\s*\S*\[dbo\]\.").Replace(strTemp, " FROM ");//去除FROM [DrugsInvoicing].[dbo].[DepotSeat]中数据库名和dbo.
            System.Diagnostics.Debug.WriteLine(strTemp);

            strTemp = new Regex(@"SELECT\sTOP\s*\w*\s*\[").Replace(strTemp, "SELECT [");//去除top 1000
            //strTemp = new Regex(@"\]\s*,\s*\[").Replace(strTemp, "],[");
            strTemp = new Regex(@" {1,}").Replace(strTemp, " ");
            strTemp = strTemp.Replace("] ,[", "],[");

            if (chkYesSingleTable.Checked == true)
            {
                String strTableName = new Regex(@"FROM\s\[\w*\]", RegexOptions.IgnoreCase).Match(strTemp).Value;
                strTableName = strTableName.Replace("FROM [", "").Replace("]", "");
                strTemp = strTemp.Replace("SELECT [", "SELECT [" + strTableName + ".");//为Select后第一个字段加上表名
                System.Diagnostics.Debug.WriteLine(strTemp);
                strTemp = strTemp.Replace(",[", ",[" + strTableName + ".");
            }

            //取出 GROUP BY和 ORDER BY 部分,两者放在同一个数组元素中
            System.Text.RegularExpressions.Match match = new Regex(@"GROUP BY .*", RegexOptions.IgnoreCase).Match(strTemp);
            if (match.Success == true)
            {
                if (chkUprightParentheses.Checked==true){ strRel[3] = match.Value.Replace("[", "").Replace("]", "");}

                strTemp = new Regex(@"GROUP BY .*", RegexOptions.IgnoreCase).Replace(strTemp, "");//去除ORDER BY 部分
            }
            else
            {

                match = new Regex(@"ORDER BY .*", RegexOptions.IgnoreCase).Match(strTemp);
                if (match.Success == true)
                {
                    if (chkUprightParentheses.Checked==true){ strRel[3] = " " + match.Value.Replace("[", "").Replace("]", "") + " ";}
                    strTemp = new Regex(@"ORDER BY .*", RegexOptions.IgnoreCase).Replace(strTemp, "");//去除ORDER BY 部分
                }
            }

            strRel[3] = new Regex(@"GROUP BY", RegexOptions.IgnoreCase).Replace(strRel[3], " Group By");
            strRel[3] = new Regex(@"ORDER BY", RegexOptions.IgnoreCase).Replace(strRel[3], " Order By");

            //取出WHERE部分
            match = new Regex(@"WHERE .*", RegexOptions.IgnoreCase).Match(strTemp);
            if (match.Success == true)
            {
                if (chkUprightParentheses.Checked==true){ strRel[2] = " " + match.Value.Replace("[", "").Replace("]", "") + " ";}
                strTemp = new Regex(@"WHERE .*", RegexOptions.IgnoreCase).Replace(strTemp, "");//去除WHERE 部分
            }
            //出取FROM部分
            match = new Regex(@"FROM .*", RegexOptions.IgnoreCase).Match(strTemp);
            if (chkUprightParentheses.Checked == true) { strRel[1] = " " + match.Value.Replace("[", "").Replace("]", "") + " "; }
            strTemp = new Regex(@"FROM .*", RegexOptions.IgnoreCase).Replace(strTemp, "");//去除WHERE 部分

            //得到Select部分
            if (chkUprightParentheses.Checked==true){  strRel[0] = strTemp.Replace("[", "").Replace("]", "");}
            String[] strRel0 = strRel[0].Split(',');
            int length = strRel0.Length;
            strRel0[0] = strRel0[0] + "\"";
            for (int i = 1; i < length; i++)
            {
                strRel0[i] = Environment.NewLine + "                    			                   ,\"," + strRel0[i] + "\"";
            }


            strRel[0] = String.Concat(strRel0);
            String strValueName = lstValueName.SelectedItem.ToString();
            txtSqlConcat.Text = String.Concat(
                                    "                    	", strValueName, "[0]=String.Concat(\"", strRel[0], ");", Environment.NewLine
                                    , "                    	", strValueName, "[1]=\"", strRel[1], "\";", Environment.NewLine
                                    , "                    	", strValueName, "[2]=\"", strRel[2], "\";", Environment.NewLine
                                    , "                    	", strValueName, "[3]=\"", strRel[3], "\";", Environment.NewLine
                                    );
            Clipboard.SetText(txtSqlConcat.Text);


            intCleartFlag = 0;//自动清空计时标志重置为0，重新计算清空时间

        }
        
        #endregion

        #region 字符串格式化
        private void txtSqlConcat_Click(object sender, EventArgs e)
        {
            String strTemp = Clipboard.GetText();
            if (String.IsNullOrEmpty(strTemp) == true) { return; }
            strTemp = strTemp.Replace(Environment.NewLine, "\"" + Environment.NewLine + "                              ,\"");
            if (chkUprightParentheses.Checked == true) { strTemp = strTemp.Replace("[", "").Replace("]", ""); }
            strTemp = "String.Concat(\"" + strTemp + "\");";
            strTemp = lstValueName.SelectedItem.ToString() + "=" + strTemp;

            strTemp = strTemp.Replace("From", " From ");
            strTemp = strTemp.Replace("Where", " Where ");
            strTemp = strTemp.Replace("Order By", " Order By ");
            strTemp = strTemp.Replace("Group By", " Group By ");

            txtSqlConcat.Text = strTemp;
            Clipboard.SetText(txtSqlConcat.Text);
            intCleartFlag = 0;//自动清空计时标志重置为0，重新计算清空时间
        }        
        #endregion


        #region 顿号变逗号


        private void txtPunctuationChange_Click(object sender, EventArgs e)
        {
            String strTemp = Clipboard.GetText();
            if (String.IsNullOrEmpty(strTemp) == true) { return; }
            txtPunctuationChange.Text = strTemp.Replace("、", ",");
            Clipboard.SetText(txtPunctuationChange.Text);
            intCleartFlag = 0;//自动清空计时标志重置为0，重新计算清空时间
        }
        #endregion


        /// <summary> 清除所有文本框 </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void bntClearText_Click(object sender, EventArgs e)
        {
            txtDbInfo.Text = "";
            txtSqlS.Text = "";
            txtSqlConcat.Text = "";
            txtPunctuationChange.Text = "";

            txtUrlDecode.Text = "";
            txtUrlEncode.Text = "";
            txtHttpUtilityResult.Text = "";
            intCleartFlag = 0;
        }





        #region 中文翻译、英文去空格
         private void txtChinse_TextChanged(object sender, EventArgs e)
        {
            if (txtChinse.Text.Trim() != "")
            {
                txtGetEnglish.Text = BaiduTans_Get(txtChinse.Text);//自动将中文翻译成英文
                txtGetEnglish.Text = "";
            }
        }

        private void txtChinse_Click(object sender, EventArgs e)
        {
            if (txtChinse.Text.Trim() == "")
            {
                txtChinse.Text = Clipboard.GetText().Trim();
                txtChinse.Text = "";
            }
        }

        private void txtGetEnglish_TextChanged(object sender, EventArgs e)
        {
            if (txtGetEnglish.Text.Trim() != "")
            {
                txtGetEnglish2.Text = txtGetEnglish.Text.Length.ToString();//显示字符串的长度
                String[] tempStrs = txtGetEnglish.Text.Split(' ');
                txtGetEnglish1.Text = String.Concat(tempStrs.Select(r => r.Substring(0, 1).ToUpper() + r.Substring(1).ToLower()).ToArray());
                Clipboard.SetText(txtGetEnglish1.Text);
            }
        }
        private void txtGetEnglish_Click(object sender, EventArgs e)
        {
            if (txtGetEnglish.Text.Trim() == "")
            {
                txtGetEnglish.Text = Clipboard.GetText().Trim();
                txtGetEnglish.Text = "";
            }
        }

        #endregion

        #region HttpUtility编码解码


        private void txtUrlEncode_TextChanged(object sender, EventArgs e)
        {
            //原码-->编码
            if (txtUrlEncode.Text.Trim() != "") {
                txtHttpUtilityResult.Text = System.Web.HttpUtility.UrlEncode(txtUrlEncode.Text);
            }
        }

        private void txtUrlEncode_Click(object sender, EventArgs e)
        {
            //原码-->编码
            txtUrlEncode.Text = Clipboard.GetText().Trim();
        }



        private void txtUrlDecode_TextChanged(object sender, EventArgs e)
        {   //编码-->原码
            if (txtUrlEncode.Text.Trim() != ""){ txtHttpUtilityResult.Text = System.Web.HttpUtility.UrlDecode(txtUrlDecode.Text); }
        }
        private void txtUrlDecode_Click(object sender, EventArgs e)
        {
            txtUrlDecode.Text = Clipboard.GetText().Trim();
        }


        private void txtHttpUtilityResult_TextChanged(object sender, EventArgs e)
        {
            if (txtHttpUtilityResult.Text.Trim() != "")
            {
                Clipboard.SetText(txtHttpUtilityResult.Text);
                txtHttpUtilityResult.SelectAll();
            }
        }

        #endregion

        /// <summary> 测试正则表达式 </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            object aa = 2;
            Decimal bb = (System.Decimal) aa;
            //typeof
            //bb=typeof bb aa;
            //System.Text.RegularExpressions.Regex regEx = new Regex(textBox1.Text);

            //regEx.Matches(textBox2.Text).Cast<Match>().Select(r => MessageBox.Show(r.Value));

            //MessageBox.Show(regEx.Matches(textBox2.Text)[0].Value);
        }
       
            #endregion
        #endregion
        
        #region 公共方法
        private String GetConnStr()
        { 

            return String.Format("data source={0};initial catalog={1};user id={2};pwd={3}"
                        , combIP.Text, combDBName.SelectedItem.ToString()
                        , txtUser.Text, txtPassWord.Text);
        }
        private SqlConnection GetConn()
        {
             SqlConnection conn= new SqlConnection(GetConnStr());
             conn.Open();
             return conn;
        }

        private DataTable GetDataTable(String strSql)
        {
            SqlConnection conn=GetConn();
            SqlDataAdapter da=new SqlDataAdapter(strSql,conn);
            DataTable dt=new DataTable();
            da.Fill(dt);
            conn.Close();
            return dt;


        }

        private void ExeSqlStr(String strSql, Boolean bolShowMessageBox)
        {
            try
                {
                    SqlConnection conn = GetConn();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = strSql;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    conn.Close();
                    if (bolShowMessageBox==true)
                    {
                        MessageBox.Show(strSql + " 执行完毕!");
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(strSql + Environment.NewLine + ex.Message);
                    System.Diagnostics.Debug.WriteLine(strSql);
            }
        }

        /// <summary>执行一条SQl语句 </summary>
        /// <param name="strSql"></param>
        private void ExeSqlStr(String strSql)
        {
            this.ExeSqlStr(strSql, true);
        }


        /// <summary>执行多条SQL语句 </summary>
        /// <param name="lstSql"></param>
        private void ExeSqlStrS(List<String> lstSql)
        {
            SqlConnection conn = GetConn();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.Transaction = conn.BeginTransaction();
            progressBar1.Maximum = lstSql.Count;
            progressBar1.Value = 0;
            progressBar1.Visible = true;
            try
            {
                lstSql.ForEach(
                    r =>
                    {
                        cmd.CommandText = r;
                        System.Diagnostics.Debug.WriteLine(r);
                        cmd.ExecuteNonQuery();
                        progressBar1.Value++;
                    });
                cmd.Transaction.Commit();
                
            }
            catch (Exception ex)
            {
                cmd.Transaction.Rollback();
                MessageBox.Show(ex.Message);
                throw ex;
            }
            finally
            {
                progressBar1.Visible = false;
                conn.Close();
            }
        }

        /// <summary>调用百度翻译服务将中文翻译成英文 </summary>
        /// <param name="q">汉语</param>
        /// <returns>英语</returns>
        private string BaiduTans_Get(string q)
        {

            #region old
            //string strClient_id = "vG6AXgnTtYV9c0HsDpWdTZAI";
            //string strFrom = "zh";
            //String strTo = "en";

            //string url = string.Format("http://openapi.baidu.com/public/2.0/bmt/translate?client_id={0}&q={1}&from={2}&to={3}", strClient_id, q, strFrom, strTo);
            //WebClient wc = new WebClient();
            //String strJson2 = wc.DownloadString(url);

            //JObject Obj = JObject.Parse(strJson2);
            //return Obj["trans_result"][0]["dst"].ToString();
            #endregion


            try
            {
                //string strClient_id = "vG6AXgnTtYV9c0HsDpWdTZAI";
                string strFrom = "zh";
                String strTo = "en";

                String appid = "20151127000006782";
                String sec_key = "_qlXmecyWtZS70KUIeNR";
                String salt = "8080";


                #region sign的计算方法：
                #endregion



                String sign = appid + q + salt + sec_key;
                sign = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sign, "MD5").ToLower();   //32MD5加密

                q = System.Web.HttpUtility.UrlEncode(q);

                String url = string.Format("http://api.fanyi.baidu.com/api/trans/vip/translate?q={0}&from={1}&to={2}&appid={3}&salt={4}&sign={5}",
                    q, strFrom, strTo, appid, salt, sign);
                WebClient wc = new WebClient();
                String strJson2 = wc.DownloadString(url);
                JObject Obj = JObject.Parse(strJson2);
                return Obj["trans_result"][0]["dst"].ToString();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }



        }

        /// <summary>判读一个字符串是否可以转换为数字 </summary>
        /// <param name="strNumeric"></param>
        /// <returns></returns>
        private Boolean IsNumeric(String strNumeric)
        {
            Decimal decNumeric;
            return Decimal.TryParse(strNumeric, out decNumeric);
        }

        #region 操作注册表

        /// <summary>从注册表读取IP地址信息</summary>
        /// <returns>如果没有值则返回""</returns>
        private String GetRegInfo(String strkeyName)
        {
            if (IsRegeditItemExist(@"huilankeji\"+Application.ProductName) == false)
            {
                return "";
            }
            else
            {
                RegistryKey software = Registry.LocalMachine.OpenSubKey("SOFTWARE");
                RegistryKey huilankeji_cmcc = software.OpenSubKey(@"huilankeji\"+Application.ProductName);
                Object objKeyValue = huilankeji_cmcc.GetValue(strkeyName);
                String strIps = objKeyValue == null ? "" : objKeyValue.ToString();
                //if (userinfo == "") { return ""; }
                return strIps;
              

            }
        }


        /// <summary> 将IP地址信息写入注册表</summary>
        /// <param name="newValueStr">以逗号连接在一起的IP地址字符串</param>
        private void SetRegInfo(String strkeyName, String newValueStr)
        {



            RegistryKey software = Registry.LocalMachine.OpenSubKey("SOFTWARE", true);

            RegistryKey huilankeji = null;
            if (IsRegeditItemExist(@"huilankeji") == false)
            {
                huilankeji = software.CreateSubKey("huilankeji");
                huilankeji.Close();
            }
            huilankeji = software.OpenSubKey("huilankeji", true);

            RegistryKey cmcc = null;
            if (IsRegeditItemExist(@"huilankeji\" + Application.ProductName) == false)
            {
                cmcc = huilankeji.CreateSubKey(Application.ProductName);
                cmcc.Close();
            }

            cmcc = huilankeji.OpenSubKey(Application.ProductName, true);
            cmcc.SetValue(strkeyName, newValueStr);
            huilankeji.Close();
        }


        private bool IsRegeditItemExist(String keyNameStr)
        {
            //string[] subkeyNames;
            RegistryKey hkml = Registry.LocalMachine;

            RegistryKey software = hkml.OpenSubKey(@"SOFTWARE\" + keyNameStr);
            if (software == null) { return false; }
            return true;
        }

        private bool IsRegeditKeyExit(String keyNameStr)
        {
            string[] subkeyNames;
            RegistryKey hkml = Registry.LocalMachine;
            RegistryKey software = hkml.OpenSubKey("SOFTWARE");
            //RegistryKey software = hkml.OpenSubKey(SOFTWAREtest, true);
            subkeyNames = software.GetValueNames();
            //取得该项下所有键值的名称的序列，并传递给预定的数组中
            foreach (string keyName in subkeyNames)
            {
                if (keyName == keyNameStr) //判断键值的名称
                {
                    hkml.Close();
                    return true;
                }
            }
            hkml.Close();
            return false;
        }
        #endregion



        #region AES加密解密
        //private static String keyStr = "10C2012000044BBA88C15332C4DE89A3";//-32位加密
        private static String keyStr = "88C15332C4DE89A3";//-16位加密
        public  String AES_Encrypt(string toEncrypt)
        {
            if (toEncrypt == "") { return ""; }
            byte[] keyArray = UTF8Encoding.UTF8.GetBytes(keyStr);
            byte[] toEncryptArray = UTF8Encoding.UTF8.GetBytes(toEncrypt);
            RijndaelManaged rDel = new RijndaelManaged();
            rDel.Key = keyArray;
            rDel.Mode = CipherMode.ECB;
            rDel.Padding = PaddingMode.PKCS7;
            ICryptoTransform cTransform = rDel.CreateEncryptor();
            byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);
            return Convert.ToBase64String(resultArray, 0, resultArray.Length);
        }


        public  String AES_Decrypt(string toDecrypt)
        {
            if (toDecrypt == "") { return ""; }
            byte[] keyArray = UTF8Encoding.UTF8.GetBytes(keyStr);
            byte[] toEncryptArray = Convert.FromBase64String(toDecrypt);

            RijndaelManaged rDel = new RijndaelManaged();
            rDel.Key = keyArray;
            rDel.Mode = CipherMode.ECB;
            rDel.Padding = PaddingMode.PKCS7;

            ICryptoTransform cTransform = rDel.CreateDecryptor();
            byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);

            return UTF8Encoding.UTF8.GetString(resultArray);
        }



        #endregion

        #endregion


    }
}
